Create Add-In with Command Bar drop-down

  • 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

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



    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
  • 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

  • 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?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!