custom menu commands

schultz1414

New Member
Joined
Oct 28, 2002
Messages
17
:unsure: 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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
Here is code that will create a menu item in the toolbar...


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,371
Members
410,911
Latest member
AniEx
Top