Private Const strMenu As String = "My Menu"
Private Sub auto_open()
'=========================================
' Author: wongm003
' Purpose: create temporary menu toolbar
' when workbook opens
'=========================================
On Error Resume Next
Call create_MENU_BAR
End Sub
Private Sub auto_close()
'=========================================
' Author: wongm003
' Purpose: delete temporary menu toolbar
' when workbook closes
'=========================================
On Error Resume Next
Call delete_MENU_BAR
End Sub
Public Sub create_MENU_BAR()
'=========================================
' Author: wongm003
' Purpose: create temporary menu toolbar
'=========================================
On Error Resume Next
Dim myBar As CommandBar
Call delete_MENU_BAR
Set myBar = CommandBars.Add _
(Name:=strMenu, Position:=msoBarFloating, Temporary:=True)
With myBar
.Visible = True
.Position = msoBarTop
End With
Set ctrl1 = myBar.Controls _
.Add(Type:=msoControlButton)
With ctrl1
.Style = msoButtonIconAndCaption
.FaceId = 1648
.Caption = "My Macro"
.TooltipText = "Select to run my macro"
.OnAction = "sub_name"
End With
End Sub
Public Sub delete_MENU_BAR()
'=========================================
' Author: wongm003
' Purpose: delete cdm toolbar
'=========================================
On Error Resume Next
CommandBars(strMenu).Delete
End Sub
Public Sub sub_name()
'=========================================
'Your code here
'=========================================
msgbox "This is my macro"
End Sub