Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    I have an add-in which has been distributed to a number of users. The add-in consists of a handful of macros and a custom toolbar to call the macros. The first time the add-in is loaded, the custom toolbar appears and everything is okay.

    Now - I need to modify one of the toolbar menus - basically I am adding one more name to a list of macros from which to choose. I know that when I first loaded the add-in Excel modifed my excel.xlb file. I want to modify the add-in (xla file) and redistribute it to my users. How in the heck do I get this to work?

    Here is what I thought should work:
    > Delete the toolbar locally - hence modifying my xlb file.
    > Remove the add-in from Excel.
    > Open the xls file from which add-in was created.
    > Tools, Customize - change menus as needed. I "think" this makes the changes in the xlb file.
    > Tools, Customize, Toolbars tab, Attach to attach the custom toolbar to the xls file.
    > Save xls file.
    > Save As xla file.

    Now if I delete the custom toolbar then load the newly updated add-in, I should have my changes. I do not! Help!

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    Another way is to have code in the AddIn Module that creates and deletes the Custom Toolbar every time the AddIn is loaded. <pre>Sub Auto_Open()
    Call LoadAddInToolBar
    End Sub

    Sub AddReviewBar()
    Dim cBar As CommandBar
    Dim I As Long
    If ToolbarExist("AddInToolBar") Then 'delete original toolbar if necessary
    CommandBars("AddInToolbar").Delete
    End If
    Set cBar = CommandBars.Add("AddInToolBar", msoBarTop)
    'Your command buttons are set up here
    End Sub

    Sub Auto_Close()
    On Error Resume Next 'in case the ToolBar has been turned off for some reason
    CommandBars("AddInToolbar").Delete
    End Sub</pre>

    HTH
    Gre

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

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    Here is some more info on 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(Cancel as Boolean)
    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

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    This what is causing my frustration. I know about the XLB file. I did "Attach" and deleted the old and attached the new toolbar to the XLS file then saved the file.

    I am going to add the code you mentioned to see if that helps.

    The XLS and the XLA files are on the network. When adding the addin, Excel asks if we want to copy the xla file locally. Do you recommend keeping it on the network or copying it locally?

    Thank you!

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

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    It can be kept on the network, unless you want to use the add-in whilst not logged in (e.g. for notebook users).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    This is spooky, here's me with virtually the same problem at the same time.

    I have a .xls file with an an attached toolbar that has two buttons to run a couple of macros, the associated code is in a module in the file. The file is saved as hidden (i.e. Workbook | Hide) and is held in a network folder which is set to be the alternate start up folder. This works fine, other's can see the toolbar and the buttons do their job.

    However, when I added another button yesterday it got messy. To get the new button to show on the other PCs I had to go round each one (only 3, phew!) and delete the username8.xlb file then, once XL was running again, click View | Toolbars | mytoolbarname. Understandably, deleting the .xlb file also meant one other customisation evaporated. Only a small job to put right again but one I'd like to avoid if I can.

    Today I have tried adding in your Workbook_BeforeClose code but still can't get the extra button to appear. Can you clarify please, where the code says "YourBarsName" is this the name of the attached toolbar, or is it the name of the .xlb file? If the latter, how can I deal with the fact that my .xlb files are of the style username.xlb, i.e. everybody's system toolbar file has a different name?

    stuck

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

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    Well, I was trying to be clear!

    YourBarsName is: Your Bars name, so the NAME of your custom toolbar or menubar, NOT the name of Excel.xlb or username8.xlb. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    You shouldnt' have deleted the entire excel.xlb on those systems, just rightclick a menubar, choose customise, find your bar and select delete. Then load the file with the attached toolbar and all should be well...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    Thank you, you were being very clear because that was what I assumed but when things didn't quite work the way I expected I thought I better just check <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    stuck (but persevering)

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    I had asked about this earlier. My laptop running 2000 seems to a number of places associated with XLStart, including Documents and SettingsmeApplication DataMicrosoftExcel and Program FilesMicrosoft OfficeXLStart. I can't seem to put xlb where it will work. I just re-tried this this morning, creating another new xlb file, saving it, and it seemed to "come back" when I tried it a few minutes later. Now, hours later, when I click it, the Excel screen "blinks" but nothing from the new xlb shows up! It is in visible in Explorer, but does nothing when I open it. I am detached from the network. Any ideas? TYIA

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

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    What do you mean by "creating another new xlb file, saving it"? The xlb file is not one you create and save explicitly, it is (or should be) created and saved automatically when you customize toolbars, create custom lists etc., and you don't open it explicitly either. If you're running Excel 2002, it should be named Excel10.xlb, and its location under Windows 2000 is probably Cocuments and Settings<username>Application DataMicrosoftExcel.

  11. #11
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    I meant that I did something-added macro buttons to a toolbar and linked them to a macro-that I thought would create an xlb file, as I understood it. Then, when it failed to appear later, I hunted the .xlb down in my user profile settings and tried double clicking it to get it to "load" No soap. It was not named Excel10.xlb, just Excel.xlb. It's still there, in all its glory, useless. Would there be some security setting somewhere that would prevent these things from being used? In Office or the OS? I do not think Excel would prevent their use. Thanks.
    News Flash. KB article211924 says, in part, *This problem occurs if you do not have read/write permission to the toolbar settings (.xlb) file. To customize toolbar settings and then use the setting changes, Microsoft Excel 2000 requires that you have read/write permission to the toolbar settings file. * Perhaps that's it. Where and how do I find out about my permissions regarding .xlb files? Do I have to be an administrator? Thanks again.

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

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    Normally, these permission things are set on folder level, not on file level. You can check by trying to create a dummy text file in that folder, try to rename it, try to edit and save it and try to delete it. You'll find out what permissions you have for that folder.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    Hans,

    Not like you to use a word like 'probably'. <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

    I'm not familiar with Excel 2002 so this info is probably not relevant but XL97 under Win 2000 puts the .xlb file in the main windows folder (C:WINNT in my case) and not in Cocuments and Settings etc.

    Ken

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

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    Hi Ken,

    The name and location of the .xlb file depends on
    - The version of Excel
    - The version of Windows
    - Whether you're logged in to a network.

    For Excel 97, the location is indeed the Windows folder (C:Windows or C:WinNT depending on the Windows version), and the name is Excel8.xlb in a standalone installation, or <username>8.xlb if you're on a network. At least, that was my experience when I used Excel 97.

    For Excel 2002 on Windows XP, it is Excel10.xlb in Cocuments and Settings<username>Application DataMicrosoftExcel.

    I haven't tried Excel 2002 on other windows versions and Excel 2000 not at all, therefore the "probably".

  15. #15
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Toolbar/Excel Add-in (Excel 2002/SP2)

    My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> based on my experiences with XL97 + Win 2000:

    When you open XL97 it automatically creates a file called username.xlb in the main windows folder (C:WINNT in my case). If you make any changes to toolbars or create a new custom toolbar then these changes and new toolbars are written to the .xlb file so that the next time you open XL97 your changes etc are still available. This is fine if you are just working on your PC and for your own entertainment. but it means if you want to share a toolbar you have created you have to find a way of getting the toolbar info out of your auto created .xlb and into other peoples auto created .xlb files.

    A very crude method is to simply use Explorer to overwrite other peoples .xlb file with a copy of your .xlb file. Not really an ideal method as it means everybody else's toolbar customisations evaporate and if you have lots of other users it takes ages. However, it does work, I've been there.

    The 'proper' way to do it appears to be:
    1) write the macros and save them in a workbook, e.g. CommomCode.xls
    2) create a toolbar and associate the buttons with the relevant macro code
    3) attach the new toolbar to the workbook via the Attach button on the 'Customize' Dialog
    4) save the file again to make sure everything is bundled together
    5) put this CommonCode.xls file into everybody's XLSTART folder or if you set everybody's alternate startup folder to be a network location it's easier to put in that single location.

    As everybody then opens XL the CommonCode.xls file will open and the new toolbar will be available from View Toolbars. If you don't want everybody else to see the CommonCode.xls file when they open XL then use then save it as a hidden file from within XL using Workbook Hide before you propagate it to other people. From Pieterse's earlier post I now know that at this point XL checks its .xlb file and if the toolbar attached to CommonCode.xls is not present it copies it into its .xlb file.

    My problem is also to do with updating the toolbar on other peoples PCs. I added new code and a new button to run the code. Although I saved the code to CommonCode.xls and made sure I reattached the updated toolbar to it when I propagated it to everybody else the new button did not appear. Pieterse's earlier post seems to hold the answer. To get other peoples .xlb files to auto update it is necessary to delete the toolbar from their system so that the next time CommonCode.xls is opened the toolbar is not present so the latest version is copied across. However, I'm still trying to get the theory to work in practice.

    Finally as the .xlb file is autogenerated it seems likely that you do have the necessary permissions for the relevant folder

    stuck

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
  •