[已解决]如何封装2003版Excel自定义工作表菜单

南海居士

本人设计想把2003版Excel自定义工作表菜单封装成DLL，均不成功，按网上下载的一篇文件操作，仍不能引用Sub程序，企盼高手诊断一下网上这篇文章的错漏，不胜感激！！！

最佳答案

月排行榜 / 总排行榜

hwc2ycy

2013-12-26 10:55

另外插入一个模块

1. Option Explicit
   
2. 
   
3. Public xlApp As Excel.Application
   
4. 
   
5. Public Sub 部门()
   
6.     With xlApp
   
7.         .Selection.Value = "部门"
   
8.     End With
   
9. End Sub
   
10. Public Sub 工程部()
    
11.     With xlApp
    
12.         .Selection.Value = "工程部"
    
13.     End With
    
14. End Sub
    
15. Public Sub 生产部()
    
16.     With xlApp
    
17.         .Selection.Value = "生产部"
    
18.     End With
    
19. End Sub
    
20. Public Sub 组装线()
    
21.     With xlApp
    
22.         .Selection.Value = "组装线"
    
23.     End With
    
24. End Sub
    
25. Public Sub 包装线()
    
26.     With xlApp
    
27.         .Selection.Value = "包装线"
    
28.     End With
    
29. End Sub
    
30. Public Sub 签名()
    
31.     With xlApp
    
32.         .Selection.Value = "签名"
    
33.     End With
    
34. 
    
35. End Sub
    
36. Public Sub 按钮1()
    
37.     MsgBox "你按下了按钮1"
    
38. End Sub

复制代码

跳转到最佳答案楼层

hwc2ycy

[已解决]用VB6.0制作 EXCEL自定义菜单(COM加载项)新建E文件E表格问题
http://www.excelpx.com/thread-305767-1-1.html

[已解决]VB中制作COM加载项时，如何添加子菜单
http://www.excelpx.com/thread-305423-1-1.html

[已解决]求助：用VB6.0制作 EXCEL自定义菜单(COM加载项)弹出的窗体中新建E表格问题
http://www.excelpx.com/thread-305807-1-1.html

[已解决]用VB6.0制作 EXCEL自定义菜单(COM加载项)新建E文件E表格问题
http://www.excelpx.com/thread-305779-1-1.html

你参考人家的改下就知道了，这个资料应该漏了那几个按钮的声明了。

hwc2ycy

就写在Connect模块里

1. Implements IDTExtensibility2
   
2. Public xlApp As Excel.Application
   
3. Dim WithEvents objButton1 As Office.CommandBarButton
   
4. Dim WithEvents objButton2 As Office.CommandBarButton
   
5. Dim objButton3 As Office.CommandBarControl
   
6. Dim WithEvents objButton4 As Office.CommandBarButton
   
7. Dim objButton5 As Office.CommandBarControl
   
8. Dim WithEvents objButton6 As Office.CommandBarButton
   
9. Dim WithEvents objButton7 As Office.CommandBarButton
   
10. 
    
11. Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
    
12. '相当于Excel的Workbook_Open事件
    
13.     Set xlApp = Application
    
14.     CreateMenus  '创建2003菜单
    
15. End Sub
    
16. Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    
17. '关闭时或不引用时（断开）  相当于Exce的Workbook_BeforeClose(Cancel As Boolean)事件
    
18.     On Error Resume Next
    
19.     xlApp.CommandBars("Worksheet Menu Bar").Controls("自定义工具(&K)").Delete
    
20. End Sub
    
21. Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)   '占位
    
22. End Sub
    
23. Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)   '占位
    
24. End Sub
    
25. Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)   '占位
    
26. End Sub
    
27. Private Sub IDTExtensibility_OnStartupComplete(custom() As Variant)   '占位
    
28. End Sub
    
29. Private Sub CreateMenus()    '创建自定义工具栏
    
30.     On Error Resume Next
    
31.     xlApp.CommandBars("Worksheet Menu Bar").Controls("自定义工具(&K)").Delete
    
32.     With xlApp.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Before:=11)  '创建一个新工具栏
    
33.         .Caption = "自定义工具(&K)"
    
34.         .Style = msoButtonIconAndCaption
    
35.         Set objButton1 = .Controls.Add(Type:=msoControlButton)    '创建按钮
    
36.         With objButton1    '引用子菜单
    
37.             .Caption = "按钮1"    '设置菜单的显示文字
    
38.             .Style = msoButtonIconAndCaption    '同时显示文字与图标
    
39.             .FaceId = 81    '指定图标
    
40.         End With
    
41.         Set objButton2 = .Controls.Add(Type:=msoControlButton)    '创建第二个按钮
    
42.         With objButton2
    
43.             .Caption = "签名"
    
44.             .Style = msoButtonIconAndCaption
    
45.             .FaceId = 82
    
46.         End With
    
47.         Set objButton3 = .Controls.Add(Type:=msoControlPopup)    '创建第三个按钮为二级菜单
    
48.         With objButton3
    
49.             .Caption = "部门"
    
50.             .FaceId = 83
    
51.             Set objButton4 = .Controls.Add(Type:=msoControlButton)    '创建第四个按钮为二级菜单一
    
52.             With objButton4
    
53.                 .Caption = "工程部"
    
54.                 .Style = msoButtonIconAndCaption
    
55.                 .FaceId = 84
    
56.             End With
    
57.             Set objButton5 = .Controls.Add(Type:=msoControlPopup)    '创建第五个按钮为三级菜单
    
58.             With objButton5
    
59.                 .Caption = "生产部"
    
60.                 .Style = msoButtonIconAndCaption
    
61.                 .FaceId = 85
    
62.                 Set objButton6 = .Controls.Add(Type:=msoControlButton)    '创建第六个按钮 为三级菜单一
    
63.                 With objButton6
    
64.                     .Caption = "组装线"
    
65.                     .Style = msoButtonIconAndCaption
    
66.                     .FaceId = 86
    
67.                 End With
    
68.                 Set objButton7 = .Controls.Add(Type:=msoControlButton)    '创建第六个按钮 为三级菜单二
    
69.                 With objButton7
    
70.                     .Caption = "包装线"
    
71.                     .Style = msoButtonIconAndCaption
    
72.                     .FaceId = 87
    
73.                 End With
    
74.             End With
    
75.         End With
    
76.         xlApp.ScreenUpdating = True
    
77.         .Visible = True
    
78.     End With
    
79. End Sub
    
80. 
    
81. Public Sub objButton1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
82.     按钮1
    
83. End Sub
    
84. Public Sub objButton2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
85.     签名
    
86. End Sub
    
87. Public Sub objButton3_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
88.     部门
    
89. End Sub
    
90. Public Sub objButton4_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
91.     工程部
    
92. End Sub
    
93. Public Sub objButton5_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
94.     生产部
    
95. End Sub
    
96. Public Sub objButton6_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
97.     组装线
    
98. End Sub
    
99. Public Sub objButton7_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
100.     包装线
     
101. End Sub
     
102. 
     
103. Public Sub 部门()
     
104.     With xlApp
     
105.         .Selection.Value = "部门"
     
106.     End With
     
107. End Sub
     
108. Public Sub 工程部()
     
109.     With xlApp
     
110.         .Selection.Value = "工程部"
     
111.     End With
     
112. End Sub
     
113. Public Sub 生产部()
     
114.     With xlApp
     
115.         .Selection.Value = "生产部"
     
116.     End With
     
117. End Sub
     
118. Public Sub 组装线()
     
119.     With xlApp
     
120.         .Selection.Value = "组装线"
     
121.     End With
     
122. End Sub
     
123. Public Sub 包装线()
     
124.     With xlApp
     
125.         .Selection.Value = "包装线"
     
126.     End With
     
127. End Sub
     
128. Public Sub 签名()
     
129.     With xlApp
     
130.         .Selection.Value = "签名"
     
131.     End With
     
132. 
     
133. End Sub
     
134. Public Sub 按钮1()
     
135.     MsgBox "你按下了按钮1"
     
136. End Sub

复制代码

hwc2ycy

如果分成两个模块，这么写：
设计器Connect代码：

1. Option Explicit
   
2. 
   
3. Implements IDTExtensibility2
   
4. Dim WithEvents objButton1 As Office.CommandBarButton
   
5. Dim WithEvents objButton2 As Office.CommandBarButton
   
6. Dim objButton3 As Office.CommandBarControl
   
7. Dim WithEvents objButton4 As Office.CommandBarButton
   
8. Dim objButton5 As Office.CommandBarControl
   
9. Dim WithEvents objButton6 As Office.CommandBarButton
   
10. Dim WithEvents objButton7 As Office.CommandBarButton
    
11. 
    
12. Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
    
13. '相当于Excel的Workbook_Open事件
    
14.     Set xlApp = Application
    
15.     CreateMenus  '创建2003菜单
    
16. End Sub
    
17. Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    
18. '关闭时或不引用时（断开）  相当于Exce的Workbook_BeforeClose(Cancel As Boolean)事件
    
19.     On Error Resume Next
    
20.     xlApp.CommandBars("Worksheet Menu Bar").Controls("自定义工具(&K)").Delete
    
21. End Sub
    
22. Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)   '占位
    
23. End Sub
    
24. Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)   '占位
    
25. End Sub
    
26. Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)   '占位
    
27. End Sub
    
28. Private Sub IDTExtensibility_OnStartupComplete(custom() As Variant)   '占位
    
29. End Sub
    
30. Private Sub CreateMenus()    '创建自定义工具栏
    
31.     On Error Resume Next
    
32.     xlApp.CommandBars("Worksheet Menu Bar").Controls("自定义工具(&K)").Delete
    
33.     With xlApp.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Before:=11)  '创建一个新工具栏
    
34.         .Caption = "自定义工具(&K)"
    
35.         .Style = msoButtonIconAndCaption
    
36.         Set objButton1 = .Controls.Add(Type:=msoControlButton)    '创建按钮
    
37.         With objButton1    '引用子菜单
    
38.             .Caption = "按钮1"    '设置菜单的显示文字
    
39.             .Style = msoButtonIconAndCaption    '同时显示文字与图标
    
40.             .FaceId = 81    '指定图标
    
41.         End With
    
42.         Set objButton2 = .Controls.Add(Type:=msoControlButton)    '创建第二个按钮
    
43.         With objButton2
    
44.             .Caption = "签名"
    
45.             .Style = msoButtonIconAndCaption
    
46.             .FaceId = 82
    
47.         End With
    
48.         Set objButton3 = .Controls.Add(Type:=msoControlPopup)    '创建第三个按钮为二级菜单
    
49.         With objButton3
    
50.             .Caption = "部门"
    
51.             .FaceId = 83
    
52.             Set objButton4 = .Controls.Add(Type:=msoControlButton)    '创建第四个按钮为二级菜单一
    
53.             With objButton4
    
54.                 .Caption = "工程部"
    
55.                 .Style = msoButtonIconAndCaption
    
56.                 .FaceId = 84
    
57.             End With
    
58.             Set objButton5 = .Controls.Add(Type:=msoControlPopup)    '创建第五个按钮为三级菜单
    
59.             With objButton5
    
60.                 .Caption = "生产部"
    
61.                 .Style = msoButtonIconAndCaption
    
62.                 .FaceId = 85
    
63.                 Set objButton6 = .Controls.Add(Type:=msoControlButton)    '创建第六个按钮 为三级菜单一
    
64.                 With objButton6
    
65.                     .Caption = "组装线"
    
66.                     .Style = msoButtonIconAndCaption
    
67.                     .FaceId = 86
    
68.                 End With
    
69.                 Set objButton7 = .Controls.Add(Type:=msoControlButton)    '创建第六个按钮 为三级菜单二
    
70.                 With objButton7
    
71.                     .Caption = "包装线"
    
72.                     .Style = msoButtonIconAndCaption
    
73.                     .FaceId = 87
    
74.                 End With
    
75.             End With
    
76.         End With
    
77.         xlApp.ScreenUpdating = True
    
78.         .Visible = True
    
79.     End With
    
80. End Sub
    
81. 
    
82. Public Sub objButton1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
83.     按钮1
    
84. End Sub
    
85. Public Sub objButton2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
86.     签名
    
87. End Sub
    
88. Public Sub objButton3_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
89.     部门
    
90. End Sub
    
91. Public Sub objButton4_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
92.     工程部
    
93. End Sub
    
94. Public Sub objButton5_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
95.     生产部
    
96. End Sub
    
97. Public Sub objButton6_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
98.     组装线
    
99. End Sub
    
100. Public Sub objButton7_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
     
101.     包装线
     
102. End Sub

复制代码

hwc2ycy

另外插入一个模块

1. Option Explicit
   
2. 
   
3. Public xlApp As Excel.Application
   
4. 
   
5. Public Sub 部门()
   
6.     With xlApp
   
7.         .Selection.Value = "部门"
   
8.     End With
   
9. End Sub
   
10. Public Sub 工程部()
    
11.     With xlApp
    
12.         .Selection.Value = "工程部"
    
13.     End With
    
14. End Sub
    
15. Public Sub 生产部()
    
16.     With xlApp
    
17.         .Selection.Value = "生产部"
    
18.     End With
    
19. End Sub
    
20. Public Sub 组装线()
    
21.     With xlApp
    
22.         .Selection.Value = "组装线"
    
23.     End With
    
24. End Sub
    
25. Public Sub 包装线()
    
26.     With xlApp
    
27.         .Selection.Value = "包装线"
    
28.     End With
    
29. End Sub
    
30. Public Sub 签名()
    
31.     With xlApp
    
32.         .Selection.Value = "签名"
    
33.     End With
    
34. 
    
35. End Sub
    
36. Public Sub 按钮1()
    
37.     MsgBox "你按下了按钮1"
    
38. End Sub

复制代码

南海居士

问题已经解决了，真诚感谢版主，您的专业精神很让本人敬佩！！！

yjwdjfqb

楼主，你应该给版主最佳呀，我也很多次得到版主的帮助！

南海居士

hwc2ycy 发表于 2013-12-26 10:55
另外插入一个模块

再多请教另外一个问题：如何在上述自定义菜单封装程序中，如何将另一个自定义函数合并一起封装，即这个自定义要如何设置，放在哪个地方？烦请版主教导

hwc2ycy

南海居士 发表于 2013-12-28 21:31
再多请教另外一个问题：如何在上述自定义菜单封装程序中，如何将另一个自定义函数合并一起封装，即这个自 ...

放一块或另外插入一个模块都是可以的，看个人喜好。

南海居士

hwc2ycy 发表于 2013-12-28 21:33
放一块或另外插入一个模块都是可以的，看个人喜好。

真诚感谢！！！