Macro to create a toolbar

**justaguyfromky** · 03-13-2011, 01:48 PM

I have a program that I want to distribute to a couple of other users. I have several modules in a workbook that my supervisor feels would help other manangers in our company. I have used this program for years and have made several updates and rewrites. I had to use the record macro feature to create a toolbar with one button. My question is... How can I include in the code the macro to be assigned to that button and rename the macro when you hover over the button. I am including what recording the macro did for me so far. I am using excel 2000

Application.CommandBars.Add(Name:="ACG Toolbar").Visible = True
Application.CommandBars("ACG Toolbar").Controls.Add Type:=msoControlButton, ID:=2950, Before:=1

I want to hover over the button and see the text "MENU" and have the macro "ACGMenu" macro assigned

Any help would be appreciated

Thanks

Rob

**Greg M** · 03-13-2011, 09:00 PM

Hi there,

Try the following code and see if it does what you need. I've added a few extra parameters for you to play with if you feel like experimenting a bit


Option Explicit


Sub CreateCommandBar()

'   Assign a value of "False" if you want the CommandBar to be retained when Excel closes
    Const bTEMPORARY        As Boolean = True

'   Use these constants for customising the CommandBar parameters
    Const sBAR_NAME         As String = "ACG Toolbar"
    Const iBAR_LEFT         As Integer = 100
    Const iBAR_TOP          As Integer = 200

'   Use these constants for customising the CommandBarButton parameters
    Const sBUTTON_CAPTION   As String = "    MENU     "
    Const sMACRO_NAME       As String = "ACGMenu"
    Const sTOOLTIP          As String = "Display the Menu for the ACG Application"
    Const iFACE_ID          As Integer = 44

    Dim cbr                 As CommandBar
    Dim btn                 As CommandBarButton

'   Before starting, delete any possible previous instance of the new CommandBar
    On Error Resume Next
        CommandBars(sBAR_NAME).Delete
    On Error GoTo 0

'   Create the CommandBar itself
    Set cbr = Application.CommandBars.Add(Name:=sBAR_NAME, Temporary:=bTEMPORARY)

'   Customise the new CommandBar
    With cbr
        .Visible = True
        .Left = iBAR_LEFT
        .Top = iBAR_TOP
    End With

'   Create the CommandBarButton itself
    Set btn = cbr.Controls.Add(Type:=msoControlButton)
    
'   Customise the new CommandBarButton
    With btn
        .TooltipText = sTOOLTIP
        .OnAction = sMACRO_NAME
        .Caption = sBUTTON_CAPTION
        .FaceId = iFACE_ID
        .Style = msoButtonIconAndCaption
    End With

End Sub

I hope this helps - please let me know how you get on.

Regards,

Greg M

**pike** · 03-13-2011, 09:07 PM

or maybe another way ..

    Option Explicit
Sub OpenMenu()'on open workbook
    Dim MenuObject As CommandBarPopup
    Dim MenuItem As CommandBarButton
    Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=1, Temporary:=True)
    With MenuObject
        .Caption = "ACG Toolbar"
        .TooltipText = "Menu"
    End With
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    With MenuItem
        .Caption = "ACG Toolbar"
        .OnAction = "ACGMenu"
        .FaceId = 2950
        .TooltipText = "Do Stuff"
    End With
End Sub
Sub ACGMenu()
MsgBox "Done"
End Sub
Sub deleteMenu()'To run before close
Application.CommandBars(1).Controls("ACG Toolbar").Delete
End Sub

**royUK** · 03-14-2011, 04:51 AM

Which version of Excel? If 2007 or later then Menus are not supported.

Add_Toolbar.zip

AddMenu.zip

**pike** · 03-15-2011, 03:09 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save