|
各位,求助。
我用附件中的文件的open方法实现的下图这个菜单,这个文件关闭也可以正常使用。。
现在问题是,完全退出Excel后,再打开,这个菜单就消失了,请问有什么办法可以保留,一直使用。
加载宏可以实现这个效果,但不想手动设置加载宏。在论坛搜索了用代码生成加载宏的方法,也没有成功,请各位指教。
附件:
Demo_TableTools1.0.zip
(15.38 KB, 下载次数: 5)
核心代码如下:
- Public Sub AddinInstall() '加载菜单
- Dim Arr, X
- With ThisWorkbook.Worksheets("Mainsetting")
- Arr = .Range("A1").CurrentRegion.Value '读取原有设置
- If IsArray(Arr) = False Then Exit Sub
- End With
- For X = 2 To UBound(Arr)
- If ExistsMenuBar(Arr(X, 2)) = False Then '如果菜单不存在就添加
- AddMenuBar Arr(X, 2), Arr(X, 3), Arr(X, 4), Arr(X, 5), Arr(X, 6), Arr(X, 7) '更新菜单
- End If
- Next
- End Sub
- '新建菜单
- Function AddMenuBar(ByVal myCaption As String, ByVal myTooltipText As String, ByVal myStyle As Byte, _
- ByVal myOnAction As String, ByVal myFaceid As Integer, ByVal myTag As String)
- Dim myMenuBar, newMenu, Ctrl1
- Dim myPic
- Set myMenuBar = CommandBars.ActiveMenuBar
- With myMenuBar.Controls.Add(1, , , 1, True)
-
- .Caption = myCaption
- .TooltipText = myTooltipText
- .Style = myStyle
- .OnAction = myOnAction
- If Len(myFaceid) > 0 Then .FaceId = myFaceid
- If Len(myTag) > 0 Then .Tag = myTag
- End With
- End Function
- '检测
- Private Function ExistsMenuBar(ByVal myCaption As String) As Boolean '检测菜单是否存在
- Dim Bar, Bc
- For Each Bar In Application.CommandBars.ActiveMenuBar.Controls
- If Bar.Caption = myCaption Then
- ExistsMenuBar = True
- Exit Function
- End If
- Next
- End Function
复制代码
|
|