[已解决]VB中制作COM加载项时，如何添加子菜单

hwc2ycy

1. Dim oXL As Object
   
2. Dim mnMain As Office.CommandBarPopup
   
3. Dim mnMain2 As Office.CommandBarPopup
   
4. Dim WithEvents mn1 As Office.CommandBarButton
   
5. Dim WithEvents mn2 As Office.CommandBarButton
   
6. Dim WithEvents MyButton As Office.CommandBarButton
   
7. 
   
8. Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
   
9.                                        ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
   
10.                                        ByVal AddInInst As Object, custom() As Variant)
    
11. 
    
12. 
    
13.     On Error Resume Next
    
14. 
    
15.     Set oXL = Application
    
16.     oXL.CommandBars("Worksheet Menu Bar").Controls("期末处理(K)").Delete
    
17.     Set mnMain = oXL.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Before:=11)
    
18.     mnMain.Caption = "期末处理(K)"
    
19.     Set mn1 = mnMain.Controls.Add(Type:=msoControlButton, Before:=1)
    
20.     With mn1
    
21.         .Caption = "移动1"
    
22.         .OnAction = "!<" & AddInInst.ProgId & ">"
    
23.         .FaceId = 162
    
24.         '.Visible = True
    
25.     End With
    
26.     Set mnMain2 = mnMain.Controls.Add(Type:=msoControlPopup, Before:=2)
    
27.     With mnMain2
    
28.         .Caption = "移动2"
    
29.         .BeginGroup = True    '增加间隔符
    
30.         '.OnAction = "!<" & AddInInst.ProgId & ">"
    
31.         '.FaceId = 300
    
32.         '.Visible = True
    
33.         Set mn1 = .Controls.Add(Type:=msoControlButton)
    
34.         With mn1
    
35.             .Caption = "子菜单1"
    
36.             .Visible = True
    
37.         End With
    
38.         Set mn2 = .Controls.Add(Type:=msoControlButton)
    
39.         With mn2
    
40.             .Caption = "子菜单2"
    
41.             .Visible = True
    
42.         End With
    
43. 
    
44. 
    
45.     End With
    
46. 
    
47. End Sub
    
48. 
    
49. Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
    
50.                                           AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    
51.     On Error Resume Next
    
52.     MsgBox "My Addin was disconnected by " & _
    
53.            IIf(RemoveMode = ext_dm_HostShutdown, _
    
54.                "Excel shutdown.", "end user.")
    
55.     mn2.Delete
    
56.     mn1.Delete
    
57.     mnMain.Delete
    
58.     Set mn2 = Nothing
    
59.     Set mn1 = Nothing
    
60.     Set mnMain = Nothing
    
61.     Set oXL = Nothing
    
62. End Sub
    
63. 
    
64. Private Sub mn1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
65.     MsgBox "Hello!"
    
66. End Sub
    
67. 
    
68. Private Sub mn2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
69.     MsgBox "Hi!"
    
70. End Sub
    

复制代码

hwc2ycy

hwc2ycy

因为之前类型是COMMANDBUTTON的类型，就不会有CONTROLS属性了。
必须是COMMANDPOPUP才成。

事后诸葛亮

hwc2ycy 发表于 2013-7-19 11:23

把源文件打包传给我可以么？谢谢了、。

hwc2ycy

你直接把我贴的源代码复制过去，原来的代码删除就行了。

hwc2ycy

自定义菜单(COM加载项).rar (12.21 KB, 下载次数: 76)

2013-7-19 11:32 上传

点击文件名下载附件

事后诸葛亮

hwc2ycy 发表于 2013-7-19 11:32

刚才在研究代码，没来的及说谢谢。

Before:=11    Before:=2  的是什么意思呀？

.OnAction = "!<" & AddInInst.ProgId & ">" 又是什么意思?
谢谢咯

hwc2ycy

Before 可选 Variant 一个指示新控件在命令栏上位置的数值。新控件将插入到位于此位置的控件之前。如果忽略该参数，控件将添加到指定命令栏的末端。

hwc2ycy

AddIn.progID 属性
返回对象的程序标识符。String 型，只读。
语法

表达式.progID

表达式   一个代表 AddIn 对象的变量。

示例


此示例为第一张工作表中所有 OLE 对象创建程序标识符列表。

Visual Basic for Applications
rw = 0
For Each o in Worksheets(1).OLEObjects
    With Worksheets(2)
        rw = rw + 1
        .cells(rw, 1).Value = o.ProgId
    End With
Next

hwc2ycy

这个也没给最佳呀，