在2003版本之前的Excel里使用过VBA的话,应该接触过在Excel里添加自定义菜单,使用方法和前面说的在VBE里添加菜单是类似的。
虽然现在已经有了Ribbon菜单,已经不大建议使用下拉式的菜单了,但是这个功能还是被保留了的,测试一下:
Sub TestExcelCMDB()
Dim cmdb As CommandBarControl
Application.CommandBars("Worksheet Menu Bar").Reset
Set cmdb = Application.CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup, Temporary:=True)
cmdb.Caption = "测试Excel下拉菜单"
Dim btn As CommandBarButton
Set btn = cmdb.Controls.Add(msoControlButton)
btn.Caption = "测试"
btn.OnAction = "TestOnAction"
End Sub
Sub TestOnAction()
MsgBox "TestOnAction"
End Sub
点击“测试”,执行TestOnAction。
同样的方法,我们在VBE里使用:
Sub TestAdd()
Dim cmd As CommandBarControl
Application.VBE.CommandBars(1).Reset
Set cmd = Application.VBE.CommandBars(1).Controls.Add(msoControlPopup)
cmd.Caption = "测试"
Dim btn As CommandBarButton
Set btn = cmd.Controls.Add
btn.Caption = "测试按钮"
btn.OnAction = "TestOnAction"
End Sub
Sub TestOnAction()
MsgBox "TestOnAction"
End Sub
很可惜,没有反应!
至于为什么,我也不知道原因,后来在网上查了相关资料后,都是要使用一个类模块进行转换,创建1个类模块,命名CCommandBar:
Public WithEvents cmdbe As VBIDE.CommandBarEvents
Private Sub cmdbe_Click(ByVal CommandBarControl As Object, handled As Boolean, CancelDefault As Boolean)
MsgBox 123
End Sub
在模块顶部声明:
Private cbar As CCommandBar
修改代码:
Sub TestAdd()
Dim cmd As CommandBarControl
Application.VBE.CommandBars(1).Reset
Set cmd = Application.VBE.CommandBars(1).Controls.Add(msoControlPopup)
cmd.Caption = "测试"
Dim btn As CommandBarButton
Set btn = cmd.Controls.Add
btn.Caption = "测试按钮"
Set cbar = New CCommandBar
Set cbar.cmdbe = Application.VBE.Events.CommandBarEvents(btn)
End Sub
运行后再点击按钮,能够响应单击事件。