VB6.0开发vbe插件右键菜单激活事件问题

apoul119

各位老师新年好，请教各位老师一个问题？
用vb开发vbe插件，想要达到的终极效果如 http://club.excelhome.net/thread-1055425-1-1.html
目前问题如下
为什么点击示例按钮后，程序没有反应？
如何在菜单中引用程序动作？
想要达到的效果是点击示例后，执行进程“示例”，弹出hello对话框。
vb代码附下，vb源码见附件。
程序版本：vb6.0,excel2007
盼复，再次感谢各位老师！


Dim ExVBE
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
    Set ExVBE = Application
    On Error Resume Next
    ExVBE.CommandBars("code window").Controls("代码").Delete
    Err.Clear
    With ExVBE.CommandBars("code window").Controls.Add(, , , 1)
        .Caption = "代码"
        .Tag = "123"
        .OnAction = "示例"
    End With
    ExVBE.CommandBars("Code Window").Controls(2).BeginGroup = True
    If Err Then MsgBox Err.Description, vbOKCancel, "错误"
End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    Set ExApp = Nothing
End Sub

Private Sub 示例()
    MsgBox "hello"
End Sub

apoul119

代码可以运行了，在这里感谢joforn，wxhnr，百度徒弟-E小白，以及论坛里各位大神的帖子，谢谢各位。
Dim ExVBE As VBE
Dim mcbMenuCommandBar As Office.CommandBarControl
Public WithEvents MenuHandler As CommandBarEvents

Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
    Set ExVBE = Application
    On Error Resume Next
    ExVBE.CommandBars("code window").Controls("代码").Delete
    Err.Clear
    Set mcbMenuCommandBar = ExVBE.CommandBars("code window").Controls.Add(1, , , 1)
    With mcbMenuCommandBar
        .Caption = "代码"
        Set MenuHandler = ExVBE.Events.CommandBarEvents(mcbMenuCommandBar)
    End With
End Sub
Private Sub MenuHandler_Click(ByVal CommandBarControl As Object, handled As Boolean, CancelDefault As Boolean)
    MsgBox "hello"
End Sub

apoul119

.OnAction = "示例" '设置单击菜单项执行的子程序名称 没有反应

wxhnr

https://zhidao.baidu.com/question/2121068824373137307.html

步骤1
利用VBA编程时，有一些代码的使用频率交过，但是每次都是一成不变。能否通过代码一键输入？
勾选Excel选项中的“信任对VB项目的访问”，然后Alt+F11组合键打开VBE窗口，选择菜单“工具”——“引用”，在弹出的窗体中将勾选“Micosoft Bisual Basic For Application extensibility 5.3”复选框，如下图所示

2步骤2
选择菜单“插入”——“模块”，并输入以下代码，用于创建9个菜单。
Dim Only As New Colection
Sub auto_Open()
  Dim Obj As Object, Mtcontrol As CommandBarControl
  On Error Resume Next
  Set Obj = Application.VBE.ActiveVbProject
  If err.Number <> 0 Then MsgBox "请勾选"信任对VBA工程对象模型的访问"": Exit Sub
  With Application.VBE.CommandBars("Code Window").Controls.Add(msoControPopup, 1, , 3, 1)
    .Caption = "一键输入(&One)"
    Set MenuEvent = New Bar
    Set MyControl = .Controls.Ad(msoControlButton, 1, , , True)
    With MyControl
      .Caption = "方位(&Error)"
      .Style = msoButtonIconAndCaption
      .FaceId = 6
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Onlu.Add MenEvent
    End With
    Set MenuEvent = New Bar
    Set MyControl = .Controls.Add(msoControlButton, 1, , , True)
    With MyControl
      .Caption = "关闭刷新(&Close)"
      .Style = msoButtonIconAndCaption
      .FaceId = 32
      Set MenuEven.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Only.Add MenuEvent
    End With
    Set MenuEvent = New Bar
    Set MyControl = .Controls.Add(msoControlButton, 1, , , True)
    With MyControl
      .Caption = "打开刷新(&Open)"
      .Style = msoButtonIconAndCaption
      .FaceId = 23
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
    End With
    Set MenuEvent = New Bar
    Set MyControl = .Controls.Add(mosControlButton, 1, , , True)
    With MyControl
      .Caption = "手动计算(&Manual)"
      .Style = msoButtonIconAndCaption
      .FaceId = 25
      .BeginGroup = True
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Only.Add MenuEvent
    End With
    Set MunuEvent = New Bar
    Set NyControl = .Controls.Add(msoControlButtom, 1, , , True)
    With MuControl
      .Caption = "自动计算(A&uto)"
      .Style = msoButtonIconAndCaption
      .FaceId = 26
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Only.Add MenuEvent
    End With
    Set MenuEvent = New Bar
    Set MyControl = .Controle.Add(msoControlButton, 1, , , True)
    With MyControl
      .Caption = "禁用事件(&Enable)"
      .Style = msoButtonIconAndCaption
      .FaceId = 27
      .BeginGroup = True
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Only.Add MenuEvent
    End With
    Set MenuEvent = New Bar
    Set MyControl = .Controle.Add(msoControlButton, 1, , , True)
    With MyControl
      .Caption = "启用时间(Disble)"
      .Style = msoButtonIconAndCaption
      .FaceId = 33
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Only.Add MenuEvent
    End With
    Set MenuEvent = New Bar
    Set MyControl = .Controle.Add(msoControlButton, 1, , , True)
    With MyControl
      .Caption = "禁止提示(Ale&rts)"
      .Style = msoButtonIconAndCaption
      .FaceId = 34
      .BeginGroup = True
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Only.Add MenuEvent
    End With
    Set MenuEvent = New Bar
    Set MyControl = .Controle.Add(msoControlButton, 1, , , True)
    With MyControl
      .Caption = "允许提示(A&kerts)"
      .Style = msoButtonIconAndCaption
      .FaceId = 31
      Set MenuEvent.Myevent = Application.VBE.Events.CommandBarEvents(MyControl)
      Only.Add MenuEvent
    End With
  End With
End Sub
再次选择菜单“插入”——“模块”，并输入以下代码，用于在光标处插入代码：
Sub 插入代码(ControlName)
  Dim 行 As Long
  Application.VBE.ActiveCodePane.GetSelection 行, 0, 0, 0
  Select Case ControlName
  Case "防错(&Error)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "on error resume next"
  Case "关闭刷新(&Close)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = False"
  Case "打开刷新(&Open)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = True"
  Case "手动计算(&Manual)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = xlManual"
  Case "自动计算(A&utp)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = xlAutomatic"
  Case "禁用事件(&Enable)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = False"
  Case "启用事件(Disble)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = True"
  Case "禁止事件(&Ale&rts)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = False"
  Case "允许提示(A&lerts)"
    Application.VBE.SelectedVBComponent.CodeModule.InsertLines 行, "Application.SecreenUpdating = true"
  End Select
End Sub
选择菜单“插入”——“类模块”，将其默认名称“类1”修改为“Bar”，然后输入以下代码：
Public WithEvents Myevent As VBIDE.CommandBarEvents
Private Sub Myevent_Click(ByVal COmmandBarControl As Object, Handled As Boolean, CancelDefault As Boolean)
  On Error Resume Next
  Call 插入代码(COmmandBarControl.Caption)
  CancelDefault = True
End Sub
将工作表保存为加载宏，并保存在自启动文件夹中。加载宏的格式和文件夹路径有Excel的版本决定，关闭Excel后并重新启动，按Alt+F11组合键打开VBE窗口，进入当前加载宏以外的任意工作簿的过程中，选择菜单“插入”——“模块”，然后在新模块代码窗口中单击鼠标右键，可以看到插件所产生的菜单

3步骤3
在模块的代码窗口中任意输入一段代码，然后像下图那样选择需要插入防错代码的代码行，单击鼠标右键，从菜单中选择“一键输入”——“防错（Error）”，那么程序将对当前选择的行插入一句代码“On Error Resume Next”

4步骤4
结果如下图所示

5步骤5
再次选择For语句行，然后通过菜单“关闭刷新(Close)”插入关闭刷新的代码，并选择最后一行插入恢复刷新的代码，执行结果如下图所示

apoul119

wxhnr 发表于 2017-1-21 14:08
https://zhidao.baidu.com/question/2121068824373137307.html

步骤1

试过了 没有反应 不知道问题出在哪里，这段代码是针对excel里的VBE的，不是在VB里的代码，我改一下放到VB中，连右键菜单也没法显示，在vbe中测试，也是有错误不能识别，感谢您的关注，盼能针对问题，给出针对性的代码，再次感谢！

apoul119

做了类，还是没有反应，有老师帮我看看哪里错了吗？
Dim ExVBE
Dim Only As New Collection
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
    Set ExVBE = Application
    Set MenuEvent = New Bar
    Set MyControl = ExVBE.CommandBars("code window").Controls.Add(1, , , 1)
    With MyControl
        .Caption = "代码"
        Set MenuEven.Myevent = ExVBE.Events.CommandBarEvents(MyControl)
        Only.Add MenuEvent
    End With
End Sub

建立了类，命名为bar，代码如下：
Public WithEvents Myevent As VBIDE.CommandBarEvents
Private Sub Myevent_Click(ByVal commandBarsControl As Object, Handled As Boolean, CancelDefault As Boolean)
    Select Case Control.Caption
    Case "代码"
        MsgBox "1999"
    End Select
End Sub

joforn

直接在设计器代码里用Private withevents XXX as YYY
代码中用Set XXX=*.CommandBars("*").Controls.Add(*)

这样不直观一些么？为什么还要另建一个类？

apoul119

joforn 发表于 2017-1-22 09:42
直接在设计器代码里用Private withevents XXX as YYY
代码中用Set XXX=*.CommandBars("*").Controls.Add(* ...

老师您好，感谢您的关注，其实我不太懂，现在的代码也是百度出来的，想实现这样一个功能，您能给我一个完整的代码吗？

apoul119

joforn 发表于 2017-1-22 09:42
直接在设计器代码里用Private withevents XXX as YYY
代码中用Set XXX=*.CommandBars("*").Controls.Add(* ...

老师 有没有 利用VB开发VBE右键菜单的书籍或例子，提供下也好，谢谢。

apoul119

joforn 发表于 2017-1-22 09:42
直接在设计器代码里用Private withevents XXX as YYY
代码中用Set XXX=*.CommandBars("*").Controls.Add(* ...

老师，我修改了下代码，还是没有反应，请你再帮我看看，问题出在哪里？谢谢。
Dim ExVBE
Dim mcbMenuCommandBar As Office.CommandBarControl
Public WithEvents MenuHandler As CommandBarEvents
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
    Set ExVBE = Application
    On Error Resume Next
    ExVBE.CommandBars("code window").Controls("代码").Delete
    Err.Clear
    Set mcbMenuCommandBar = ExVBE.CommandBars("code window").Controls.Add(1, , , 1)
    With mcbMenuCommandBar
        .Caption = "代码"
        Set MenuHandler = VBInstance.Events.CommandBarEvents(mcbMenuCommandBar)
    End With
End Sub
Private Sub MenuHandler_Click(ByVal CommandBarControl As Object, handled As Boolean, CancelDefault As Boolean)
    MsgBox "hello"
End Sub