Adding Menu Item to Worksheet Menu Bar

  • Hi,

    I'd like to Add a new menu item to the Worksheet Menu Bar. The caption on the new menu should be Cost Statement. Within the new menu item, I'd like to add the following buttons.

    First Button:
    Caption: Reset Report
    Macro: ResetAll

    Second Button:
    Caption: Locate Missing IDs
    Macro: UnmatchedListing

    The new menu item needs to be added in when the ThisWorkbook opens, and the worksheet menubar is to be reset each time ThisWorkbook closes.

    Any help would be highly appreciated.



    PS: ThisWorkbook is a normal excel file (xls) - Not an Add-in

  • Re: Adding Menu Item to Worksheet Menu Bar

    The best way I've found to start on something like this is to turn on the macro recorder and do it manually.

    Then you can alter the generated code if needed.


  • Re: Adding Menu Item to Worksheet Menu Bar

    Thanks for the suggestion, Norie.

    I've already tried the record-and-run approach. Unfortunately, I get an illegal operation error when I try to run the recorded code.

    I have the option of creating a custom toolbar and attaching it to the workbook. However, I'd like to generate the commandbar programmatically so that the user cannot simply delete/alter its settings.


  • [Solved]Re: Adding Menu Item to Worksheet Menu Bar

    Thanks for the help guys!

    dacat1997: You Rock! Thanks to you, I have a fighting chance to deliver my project on time.

    Norie: My problem's solved. But here's the code that was generated when I recorded the macro to add a new menu to the Worksheet Menu Bar. FYI, it creates a new menu item, but does not take up the caption entry made during the recording process.

    A million thanks once again.


  • Re: Adding Menu Item to Worksheet Menu Bar


    Here's code to put a menu on the standard menu bar when the workbook is opened - complete with two buttons (ctrl1 and ctrl2), and delete the menu item when the workbook is closed. (I think that's what you wanted!)

    Some info I got from the help files, some from a book but mostly from C. Pearsons' pages at: His web site is invaluable if you're trying to learn VBA!

    These two go in the 'This Workbook' section:

    And these two are just example macros (named as you asked) for anyone else who may want to try this code "as is". Put them in a standard module for the code to work "as is". Or edit the above code to run your own macros. (You can also change the menu name, item names, etc., of course - see the comments inside the macros.

    Sub resetall()
        MsgBox "resetall"
    End Sub
    Sub unmatchedlisting()
        MsgBox "unmatchedlisting"
    End Sub



Participate now!

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