随笔- 567 文章- 0 评论- 0 阅读- 55万

VBA 如何在菜单栏上添加按钮

主菜单上添加自定义按钮

Sub addbtn()
    Set myMenu = Application.CommandBars("worksheet menu bar")
    Set Button = myMenu.Controls.Add(Type:=msoControlButton)
    Button.Caption = "Caption"            '按钮上的文字，填写你需要的
    Button.Style = msoButtonIconAndCaption
    Button.FaceId = FaceId                  '按钮图标,数字比如8，系统存在的
    Button.OnAction = "OnAction"       '按钮执行的宏名，填写你自己的宏名
End Sub

If C < 7 Or C > 8 Or r < 6 Then Exit Sub
如果上面结果为true,那么Application.EnableEvents = False,以后就不现响应 Worksheet_Change,
改为下面代码试一下:
    If C < 7 Or C > 8 Or r < 6 Then
    Application.EnableEvents = True
    Exit Sub
    End If

你好:
 
請參考:
 
1.新增一個增益級專案(COM Addin)取名為 AddinXL,並寫入以下程式碼
 
Option Explicit
Implements IDTExtensibility2
Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
End Sub
Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
End Sub
Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
Set xlapp = Application
Set MenuEvents = New ToolsMenuEvents
MenuEvents.CreateMenuItems
End Sub
Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
MenuEvents.DeleteMenuItems
End Sub
Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
End Sub
2.新增一個Class 模組,取名為 ToolsMenuEvents,並寫入以下程式碼
 
Private WithEvents btnMenuItem1 As Office.CommandBarButton
Private WithEvents btnMenuItem2 As Office.CommandBarButton
Private WithEvents btnMenuItem As Office.CommandBarButton
Sub CreateMenuItems()
With xlapp.CommandBars(1).Controls.Add(Type:=msoControlPopup, Temporary:=True)
.Caption = "MyToolBar"
Set btnMenuItem1 = .Controls.Add
With btnMenuItem1
.Caption = "計算"
.BeginGroup = True
End With
Set btnMenuItem2 = .Controls.Add
With btnMenuItem2
.Caption = "統計"
.BeginGroup = False
End With
End With
End Sub
Sub DeleteMenuItems()
On Error Resume Next
xlapp.CommandBars(1).Controls("MyToolBar").Delete
End Sub
Private Sub btnMenuItem1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
MsgBox "計算"
End Sub
Private Sub btnMenuItem2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
MsgBox "統計"
End Sub
3.新增一個Module,並宣告以下變數
 
Option Explicit
Option Private Module
Public xlapp As Excel.Application
Public MenuEvents As ToolsMenuEvents
4.將此專案名稱更名為TestToolsMenuEvents
完成後如附圖所示