Permanent Custom Menu

  • I am working on a permanent custom menu that will be used for a lot of the things that I do on a daily basis. Run reports, open specific workbooks, etc. I want this to be a permanent menu, meaning that it is not tied to a specific workbook. I assume an addin is the easiest way to achieve this, but I'm not sure.

    I have most of it completed, but I would like to have a menu entry that deletes/replaces the menu in the event that there are changes made to the menu. This is the part that I can't figure out. The code, as I have it now:

    Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
    With MenuItem
        .Caption = "&Reload Menu"
        .OnAction = "ReloadMenu"
        .BeginGroup = True
    End With

    And the ReloadMenu macro:

    Sub ReloadMenu()
    Workbooks.Open ("U:\RMCMenu.xls")
    End Sub

    It's giving me an unknown error, and I'm not sure why. Application-defined or object-defined is the explanation I get. Right now I have this as just an xcel sheet, but I would eventually like to code it as an addin, if I can ever figure out how to do so.

    Can anyone help me out with this? The menu is being written for Excel 2003, but I would like it to be as compatible as possible, just in case.

  • Re: Permanent Custom Menu

    Ok, I got this working. Not sure how, but when I changed it to an addin, everything was working just fine. Next Question:

    Is it possible to put a separator of some sort into the menu bar? Not into the menus themselves, that I know how to do. Something like this, though. Picture the top of Excel:

    ... Data Window Help | New Menu1 ...

    Is this possible?

  • Re: Permanent Custom Menu

    .BeginGroup = True

    ...doesn't work.

    I asked this before and never got an answer, so I assume it can't be done.

  • Re: Permanent Custom Menu

    Came in this morning, and while my menu was still there, none of the macros worked until I reloaded the menu from the file itself. Is there any way to make it so that the menu and its associated macros will work no matter what? I'm guessing that it was the reboot at the end of the night last night that cleared it out.

  • Re: Permanent Custom Menu

    Now I'm totally confused...

    The behavior (Application-defined or object-defined error) that I was experiencing yesterday that suddenly went away has now come back...

    I'm really lost as to how to fix this. I'm intending this menu to be used by multiple people and it seems like an unnecessary step to have to email it out to them every time I make a change. I would like for them to be able to click on the Reload Menu option in the menu and have it call up the new version of the menu. I had a menu before that did this, but unfortunately I no longer have access to that code so I can't see what I my have missed.

Participate now!

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