VBA onaction传参的问题
目的是自己做个导航工具栏，在表单多的时候，可

以快速跳转

Sub initToolbar()
deleteBar
Dim cmdBar As CommandBar
Dim shtIndex As Integer
shtIndex = 0

Set cmdBar = Application.CommandBars.Add
With cmdBar
    .Name = "My Bar"
    .Position = msoBarFloating
    .Visible = True
    Set btnPop = .Controls.Add(Type:=msoControlPopup)
        With btnPop
            .Caption = "Go to.."
            shtIndex = Application.Worksheets.Count
            For i = 1 To shtIndex
                Set cmdBtn = .Controls.Add(msoControlButton)
                With cmdBtn
                    .Caption = Application.Worksheets.Item(i).Name
                    .OnAction = "btnclick(i)"
                    ''问题出在上面一句，搜了一下网页，高手们说可以自定一个msoControlButton的事件。到底怎么写呢。高手么能不能给个示例……非常感谢
                End With
            Next
        End With
End With
End Sub
sub btnclick(i as Integer)
    Application.Worksheets.Item(i).activate
End Sub

也试着向这样做了。但是还是报错。

Private WithEvents mybtn As CommandBarButton

Private Sub Workbook_Open()
initToolbar
End Sub

Private Sub mybtn_onclick()
    show (1)
End Sub

Sub show(i As Integer)
    MsgBox ("1")
End Sub



Sub initToolbar()
deleteBar
Dim cmdBar As CommandBar
Dim shtIndex As Integer
shtIndex = 0

Set cmdBar = Application.CommandBars.Add
With cmdBar
    .Name = "My Bar"
    .Position = msoBarFloating
    .Visible = True
    Set btnPop = .Controls.Add(Type:=msoControlPopup, Before:=1)
        With btnPop
            .Caption = "Go to.."
            shtIndex = Application.Worksheets.Count
            For i = 1 To shtIndex
                Set mybtn = .Controls.Add(msoControlButton)
                With mybtn
                    .Visible = True
                    .Caption = Application.Worksheets.Item(i).Name
                    .OnAction = mybtn_onclick
                End With
            Next
        End With
End With
End Sub

Sub deleteBar()
On Error Resume Next
    Application.CommandBars("My Bar").Delete
End Sub

VBA onaction 参数
[解决办法]
1) 研究下 msoControlDropdown，用这个感觉更合适。

2）用msoControlDropdown，在选择某项的时候，先检查是否已删除，如果删除给个提示。

3）删除sheet，参考http://www.officefans.net/cdb/viewthread.php?tid=7638