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

    Personal.xls with menu (excel xp)

    I want to save a custom menu that call different macros in my personal.xls. When i give my personal.xls to another user they don't see my menu. What do i have to do to bring the menu along in my personal.xls? thanks

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Personal.xls with menu (excel xp)

    I think custom menus are stored in your .xlb file (don't know what it's called in xp, look for an .xlb file stored when you last shut down your computer). Make a copy and get the other user to run this after they've opened XL. <img src=/S/electric.gif border=0 alt=electric width=15 height=15> BE WARE - it will be saved as the .xlb file on that machine when it is shut down, if the owner will want to go back to their original toolbars etc, they should make a copy of their .xlb file before running the one you give them. <img src=/S/electric.gif border=0 alt=electric width=15 height=15>

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

    Re: Personal.xls with menu (excel xp)

    The best way is to put code in the Personal.xls file Workbook Open event routine to build the menu if it does not already exist.
    Legare Coleman

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

    Re: Personal.xls with menu (excel xp)

    ok, so could i put this code in the workbook_open()... to build my custom menu but it doesn't seem to work.

    Private Sub workbook_open()
    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

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

    Re: Personal.xls with menu (excel xp)

    Yes, you should be able to do that.
    Legare Coleman

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

    Re: Personal.xls with menu (excel xp)

    Here is my code. It does work but only puts one item in my menu. I want 2 items. Could you tell me what i'm doing wrong. thanks

    The clean tables is coming under sLYNX but my clean data labels is not showing up. It only shows up if i uncomment out the 'add Type:=msocontrolButton. But then i get 2 sLYNX menus in my menu bar.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("sLYNX").Delete

    End Sub

    Private Sub Workbook_Open()


    Dim mnuBar As CommandBar
    Dim mbcontrol As CommandBarControl

    On Error Resume Next

    Application.CommandBars("sLYNX").Delete
    Set mnuBar = Application.CommandBars.Add("sLYNX")

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

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


    With mbcontrol.Controls(2)
    .OnAction = "ShowDataLabelForm"
    .Caption = "Show Data Labels"
    .FaceId = 150
    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("sLYNX").Controls(1).Move Bar:=Application. _
    CommandBars("Worksheet Menu Bar"), Before:=9 'Change 9 to a different number for different placement

    End Sub

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

    Re: Personal.xls with menu (excel xp)

    As has been said: you need to write code that creates the menubar when your workbook is opened and removes it at closure.

    But don't put it in Personal.xls if you want to distribute this.
    Also don't copy your xlb file to others.
    I would be very distressed if you would come and hijack my personal.xls and excel.xlb files!

    What you should do is create a new workbook with all your code and distribute *that* file (maybe saved as add-in).

    About toolbars:

    You can attach a toolbar to a workbook. When this workbook is loaded, XL checks if the toolbar is on the system. If not, it copies the toolbar from the workbook to the system.

    After creating *or changing* the toolbar, you should attach the toolbar to your workbook:

    - activate the workbook to which you want to attach the toolbar
    - Rightclick the toolbar, select 'customize'
    - Click 'Attach' (Toolbars Tab)
    - If the workbook already contains a toolbar by that name, delete it first by clicking on it on the righthand side and choosing Delete.
    - Select your toolbar (on the left) and press 'copy'
    - Save the workbook (optionally: save_as an add-in).

    Also, You should include code that deletes the toolbar when your workbook or add-in is closed, so that when you deliver a new version of your workbook the new toolbar will be used i.s.o the old one. You can do that in the Thisworkbook module, using the Workbook_beforeClose event:

    Private Sub Workbook_beforeClose()
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Personal.xls with menu (excel xp)

    You have a quote in front of the second, third, and fourth .Add Type:=msoControlButton statements which makes them comments.
    Legare Coleman

Posting Permissions

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