Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    valkyri
    Guest

    Custom Menus in Excel 2000

    I'm using Excel 2000, and trying to do something that I never had a problem with in 97. I am creating a custom menu. I am putting it in the toolbar. I add menu items and attach them to macros. Normally while assigning the macros at some point, Excel will crash, losing all my unsaved sheets. That's not even the worst part of the problem, when I do manage to create the menu without crashing, and I do save personal.xls, it is *not* there next time I open Excel. I create this menu as a HUGE time-saver.. and it's costing me more time than it is saving. Any suggestions greatly appreciated. THANKS!!
    Valkyri

  2. #2
    diegov
    Guest

    Re: Custom Menus in Excel 2000

    Hi!

    not sure if you managed to solve this one by now (the post is rather old!), but thought I would help anyway.

    The problem with the crashing - not sure what is causing it, but has happened to me as well! Usually doing the series of steps a little bit differently takes care of the problem.

    On the other hand, the toolbars "disappearing" from your Personal.xls is rather easy to fix. Once you have created the toolbar, and before you close Excel, do this:

    1) Select WINDOW - UNHIDE
    2) Select PERSONAL.XLS and click OK
    3) Select TOOLS - CUSTOMIZE
    4) Choose the TOOLBARS tab
    5) Click the ATTACH button
    6) Select the toolbars on the list from the left, then click the COPY button
    7) Click on OK
    8) Select WINDOW - HIDE
    9) Exit excel, and say YES when it asks whether to save changes to your Personal macro book

    Voila!

    Diego V

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Menus in Excel 2000

    What if it's a new drop down menu added on the main menu bar? I've created one in my personal.xls but it won't appear when i give it to other users to use. Is there a way to permanently "attach" it to my personal.xls? thank you

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom Menus in Excel 2000

    Menus are retained in a file called Excel#.xlb (# varies with version), not in your personal.xls. To distribute your menus without disrupting other users' menu customizations, distribute a template that installs your menus on their system via VBA code.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Menus in Excel 2000

    Below is some code that will create a custom menu. It originally wiped out the Excel menu bar and replaced it with the custom menu (for a custom application designed in Excel). I just modified it so that it will add the menu to the standard excel menu instead of creating a completely new command bar. It would probably be more elegant if it were just re-written, but you can get the gist. If you have trouble getting it to work, let me know and I will attach a file. Note: it was written in Excel 97

    Call this macro from an AutoNew and/or AutoOpen macro in the 'ThisWorkbook' module

    Public Sub Create_MenuItem()
    Dim mnuBar As CommandBar
    Dim mbcontrol As CommandBarControl


    On Error Resume Next

    Application.CommandBars("Sample Menu").Delete
    Set mnuBar = Application.CommandBars.Add("Sample Menu")

    Set mbcontrol = mnuBar.Controls.Add(Type:=msoControlPopup)
    mbcontrol.Caption = "&New"
    With mbcontrol.Controls
    .Add Type:=msoControlButton
    .Add Type:=msoControlButton
    .Add Type:=msoControlButton
    .Add Type:=msoControlButton
    End With

    With mbcontrol.Controls(1)
    .OnAction = "Macro1" 'Replace Macro1 with the macro you want to assign
    .Caption = "&Navigator" 'Replace with your caption
    .FaceId = 1741 'Replace with your FaceId
    End With 'Repeat these steps for each control below


    With mbcontrol.Controls(2)
    .OnAction = "Macro2"
    .Caption = "&Import"
    .FaceId = 173
    End With


    With mbcontrol.Controls(3)
    .OnAction = "Macro3"
    .Caption = "&Save Business Plan"
    .FaceId = 25
    End With

    With mbcontrol.Controls(4)
    .OnAction = "Macro4"
    .Caption = "Save Business Plan &As"
    .FaceId = 100
    End With


    Application.CommandBars("Sample Menu").Controls(1).Move Bar:=Application. _
    CommandBars("Worksheet Menu Bar"), Before:=9 'Change 9 to a different number for different placement

    End Sub

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Menus in Excel 2000

    What is an excel template? is it an .xla?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Menus in Excel 2000

    Another method you might try is to create your own add-in modifying the toolbars. Mine changes the right click menu on add-in install and removes the changes upon uninstall. Sometimes I run into problems where I need to delete my toolbar files (.xlb) and installing the add-in gives me all my changes back.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Menus in Excel 2000

    Can i put this in my personal.xls? Could you please send me an example file? I would greatly appreciate it. thank you

    Also, my menu has submenus, for instance, one menu item is "Delete something" and it has 3 different macros attached to it on a "submenu". What kind of control is that?

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom Menus in Excel 2000

    A template is an xlt. Actually a template isn't necessary, I misspoke, I meant template in the general sense, a workbook can install the menu items. Trick will be to also install any related code into other users' personal.xls; there are some threads elsewhere in this Forum on how to copy code.

    Since you seem to have two related threads going on this, one other general point; to set up a permanent menu, it is only necessary to run the menu code once; when the new menus get placed in the xlb, they will stick there. However, if you are providing a capital 'T' Template that uses a custom menu only when the Template is in use, the preferred way is to create the custom menu "on-the-fly" every time the Template is opened using the open event, and delete the menu when the Template is closed using the close event. Don't mean to confuse you, but when and how you create a menu varies depending on if you intend it to be permanent.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Menus in Excel 2000

    Hi John,

    There is no need to try and copy code over to other's personal.xls, much simpler is to have a separate workbook with all functionality in place and distribute that (possibly as an add-in).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    New Lounger
    Join Date
    Nov 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Menus in Excel 2000

    Sure, I can send you a file. Are you making a custom application, i.e., Do you want your menu to be the only menu users can access while your application is open? Or, do you just want to add another menu to the standard Excel menu bar

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Menus in Excel 2000

    I just want to add another menu to the standard toolbar. thank you for the help.

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom Menus in Excel 2000

    Agreed, but I understood that the poster wants only to add custom TB's and corresponding Macros to other user's Excel environments, not to issue a WB or Template to them. I'm confused.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    New Lounger
    Join Date
    Nov 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Menus in Excel 2000

    Here is the file with the custom menu in it. I did clean up the code a little and I added a submenu to one of the menu items. Just put the code into your workbook with the macros and everything, and it will be a stand alone... it will not need to reverence personal.xls or anything else to get the menu. Kind of like Pieter was describing.

    Having Face buttons makes the menus "pop". I put a webaddress in the code header where you can download a FaceID.xls utility from Microsoft.
    Attached Files Attached Files

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Menus in Excel 2000

    Thank you sooooo much. I am excited to know how to do this.

Page 1 of 2 12 LastLast

Posting Permissions

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