在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 SubSub 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 SubSub TestOnAction() MsgBox "TestOnAction"End Sub
很可惜,没有反应!
至于为什么,我也不知道原因,后来在网上查了相关资料后,都是要使用一个类模块进行转换,创建1个类模块,命名CCommandBar:
Public WithEvents cmdbe As VBIDE.CommandBarEventsPrivate Sub cmdbe_Click(ByVal CommandBarControl As Object, handled As Boolean, CancelDefault As Boolean) MsgBox 123End 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
运行后再点击按钮,能够响应单击事件。