Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Menu Bar in Excel (Excel 2002)

    I have created a menu in Excel using a macro to create it. The problem that I am having is that when I start up Excel my menu is still there even though my document that I wrote the macro in is not open. Is there any way around this or will it always be there? If someone could let me know if there is any other way to go about this.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Menu Bar in Excel (Excel 2002)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> ems508

    That is true. MS-Excel saves all sorts of customizations, including new User produced ToolBars, CommandBars and menus and what have you. This is to be expected. You don't want to work hard on developing a nice menu system only to find Excel leaving it behind.

    Since you are producing this menu via code is no different.

    Try to remove it before exiting MS-Excel. This is the clean up process that a developer should do once customization has been done.

    As you wrote the code to produce the menu, I will give you the chance to write the code to destroy it. But if you get stuck, we are all here to lend a hand.

    But I will want to point you to the fact that you can trap the Workbook Open and close and save events, so maybe you will want to check these out.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Menu Bar in Excel (Excel 2002)

    You can delete the menu when the workbook is closed:
    - In the Visual Basic Editor, double click ThisWorkbook for the workbook in question
    - Enter code like this (with the appropriate name inserted):

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("My Menu").Delete
    End Sub

    If you didn't create a separate command bar or toolbar, but a menu on the existing menu bar, replace the Delete instruction by

    Application.CommandBars("Worksheet Menu Bar").Controls("My Menu").Delete

    To make sure that the menu is displayed when the workbook is opened, you can do either of the following:
    - Create the menu in the Workbook_Open event procedure (also in ThisWorkbook), so that it is created anew each time the workbook is opened.
    or
    - In the Tools | Customize dialog, activate the Toolbars tab, click the Attach button, and add the menu bar to the workbook. Don't forget to save the workbook BEFORE you close it! (This will only work if you created a separate menu bar or tool bar)

  4. #4
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Bar in Excel (Excel 2002)

    I was wondering what the Workbook_Open command is for creating a menu because I have multiple steps right now for creating the menu should I have a private sub for each individual command or should it be one long one.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Menu Bar in Excel (Excel 2002)

    You can either call the macro you already have (if I understand your original question correctly) from the Workbook_Open event procedure, or copy the instructions you need into the Workbook_Open procedure. You should NOT use individual procedures for each instruction, that wouldn't work. So either

    Private Sub Workbook_Open()
    MyMacro
    End Sub

    where MyMacro is the name of the macro that creates the menu, or

    Private Sub Workbook_Open()
    Dim cbr As CommandBar
    Dim ctl As CommandBarButton
    Set cbr = CommandBars.Add("MyMenu", msoBarTop, True)
    Set ctl = ...
    ' etc.
    End Sub

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu Bar in Excel (Excel 2002)

    I prefer to use the workbook activate routine to create menus, and the workbook deactivate routine to destroy them. That way, if you have multiple workbooks open at the same time, your menu is only available when the appropriate workbook is active.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Menu Bar in Excel (Excel 2002)

    Yup, good idea. Or use On Open to create and Before Close to delete, On Activate to show and On Deactivate to hide the toolbar or menu.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •