custom menu commands

[schultz1414]

how can i use vba to add a new menu item and attach a macro to it in a workbook, and then remove it after the workbook is closed. i know you use to be able to do this using excel 97, but i am using xp.

 

[wongm003]

Here is code that will create a menu item in the toolbar...

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