Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Editing a custom toolbar (Excel XP)

    Hello.

    I want to edit a custom Excel toolbar, but whenever I edit it and close Excel, the toolbar reverts back to what it was like beforehand. I think the toolbar is located in an .XLA file, but this is not updated either.

    Thanks.

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

    Re: Editing a custom toolbar (Excel XP)

    Here is some information regarding toolbars:

    Excel keeps toolbar and menubar customizations in a file with the extension .xlb. The exact filename depends on Excel version and install, but usually is: Excel9.xlb or Excel.xlb or Username8.xlb.
    Often this file can be found in your WINDOWS directory.

    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

    So if you customise a custom toolbar that is "programmed" the way I have written above, any customisations you made manualy will be removed when Excel closes.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing a custom toolbar (Excel XP)

    Thanks for this Jan. However, I am still having trouble. How do I edit the add-in? I have tried following your steps, but I keep losing the toolbar.

    Thanks.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Editing a custom toolbar (Excel XP)

    Go to VB (alt-F11)
    In Project explorer find the addin
    Double-click to open (provide any password if prompted)
    Look through the code to find where it creates the toolbar and edit it.

    NOTE: many Addins are protected to prevent this. You might have to write to the author to get help or have him/her modify the code for you.

    Steve

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

    Re: Editing a custom toolbar (Excel XP)

    A simple way around this is to create your own custom toolbar:

    - Rightclick any toolbar and select customise
    - Select the Toolbars tab and click New
    - Enter a name
    - Now drag all buttons from the custom bar (whilst holding the control key so as to copy them) to your new custom bar (note your new bar may be hidden behind the dialog screen, drag the dialog aside to find it).

    Now if you add or modify any buttons of your own bar, they will stick.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing a custom toolbar (Excel XP)

    I cannot remember how to get a new toolbar name/text to appear on the toolbar when it is displayed. Could someone remind me, thanks.

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

    Re: Editing a custom toolbar (Excel XP)

    Do you mean a new toolbar button?
    - Select Tools | Customize...
    - Activate the Commands tab of the Customize dialog.
    - Select Macros in the list of Categories on the left hand side.
    - Select Custom Menu Item or Custom Button in the list of Commands on the right hand side.
    - Drag it to a convenient location on the toolbar.

    If that is not what you meant, please explain more clearly.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing a custom toolbar (Excel XP)

    Thank's that's a big help. Also, I wanted to change the name of 'New Menu' and can now see that that can be done in the 'Name' field.

    F

Posting Permissions

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