'创建查询工作表工具
Set myPop = myBar.Controls.Add(msoControlPopup) '创建一个popup下拉式列表控件
myPop.Caption = "查询工作表"
Dim i As Integer
For i = 1 To Sheets.Count '根据工作表的数量创建快速定向激活工作表按钮
Set myCtrl = myPop.Controls.Add(msoControlButton, 1)
myCtrl.Caption = Sheets(i).Name
myCtrl.FaceId = 289 + i
myCtrl.OnAction = "Ctrl_ButtonClick" '每个激活工作表按钮均执行同一个宏"Ctrl_ButtonClick"
Next
Set myBar = Nothing
Set myPop = Nothing
Set myCtrl = Nothing
End Sub
'查询工作表_宏代码
Public Sub ctrl_buttonclick()
Dim strA As String
strA = CommandBars.ActionControl.Caption
Sheets(strA).Activate
End Sub