Create Add-In with Command Bar drop-down

MisterT · Aug 23rd 2004

Hi,

I have a worksheet of macros and UDFs I use fairly often, and want to convert this to an excel add-in for ease of use by myself and others.

I am just unsure how to create the drop-down menu from the command bar, which will display the macro names and clicking on them will give the required functionality.

I have used userforms before, but require something different, I believe.

Very interested to hear from others who've done something similar.

Thanks in advance,

Mike

Will Riley · Aug 23rd 2004

I took this straight from John Walkenbach & adapted for my purposes as I saw no point in reinventing the wheel

Code

Sub CreateMenu() 'WillR June 2004 Create Tools Menu for file
    Dim HelpMenu As CommandBarControl
    Dim NewMenu As CommandBarPopup
    Dim MenuItem As CommandBarControl
    Dim Submenuitem As CommandBarButton


'   Delete the menu if it already exists
    Call DeleteMenu
    
'   Find the Help Menu
    Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
    
    If HelpMenu Is Nothing Then
'       Add the menu to the end
        Set NewMenu = CommandBars(1).Controls.Add _
          (Type:=msoControlPopup, _
           temporary:=True)
    Else
'      Add the menu before Help
        Set NewMenu = CommandBars(1).Controls.Add _
          (Type:=msoControlPopup, _
           Before:=HelpMenu.Index, _
           temporary:=True)
    End If


'   Add a caption for the menu
    NewMenu.Caption = "&SunSeeker"
    
'   ADD NEW MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
      (Type:=msoControlButton)
    With MenuItem
        .Caption = "&SunSeek..."
        .FaceId = 183
        .OnAction = "ShowForm" ' the macro to run when selected
    End With
'   ADD NEW MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
      (Type:=msoControlButton)
    With MenuItem
        .Caption = "&About SunSeeker..."
        .FaceId = 124
        .OnAction = "ShowAbout" ' the macro to run when selected
    End With
End Sub


'#################################################
Sub DeleteMenu()
    On Error Resume Next
    CommandBars(1).Controls("SunSeeker").Delete
End Sub

Display More

In the Add-in's ThisWorkbook code pane I place the following

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
'##################################
Private Sub Workbook_Open()
Call CreateMenu
End Sub

MisterT · Aug 23rd 2004

That is absolutely perfect. Brilliantly simple, well written, and annotated. Once again, Ozgrid comes up with the goods!

Thanks Will/John

Cheers,
Mike

MisterT · Aug 24th 2004

Just one quick question:

How do I indicate which hotkey letter is assigned to each menu item? I know how to do it in the properties GUI, but not in code, i.e.:

Choice 1 would have "1" underlined, as a hotkey.

Thanks again,

Mike

Will Riley · Aug 24th 2004

Yep, just move the ampersand (&) to be in front of the letter you want to underline...

i.e

Code

Set MenuItem = NewMenu.Controls.Add _ 
    (Type:=msoControlButton) 
    With MenuItem 
        .Caption = "&SunSeek..." 
        .FaceId = 183 
        .OnAction = "ShowForm" ' the macro to run when selected

will have SunSeek as the menuitem

whereas

Code

Set MenuItem = NewMenu.Controls.Add _ 
    (Type:=msoControlButton) 
    With MenuItem 
        .Caption = "Sun&Seek..." 
        .FaceId = 183 
        .OnAction = "ShowForm" ' the macro to run when selected

should look like SunSeek

Does this work for you?

MisterT · Aug 25th 2004

Spot on.

Thanks again.
Mike

Create Add-In with Command Bar drop-down

Participate now!

Share