Lost custom toolbars (Excel 2000)

[rdl@globalnet]

I'm puzzled, who can help? I recently loaded a spreadsheet, provided by a reputable company, which seems to have reset my toolbars to the default. No problem, I thought, I'll just restoire my backups to XLstart and start again - but nothing's changed. Is this an XL2000 trick, or is it me? It worked ok with XL97. I don't want to have to recreate all my toolbar buttons, commands, etc - can anyone tell me how I get XL to recognise the Book and Personal worksheets and not use default toolbars?

thanks

[sdckapr]

Try view - toolbars and check the toolbars you created. If you added buttons to a toolbar to customize and the spreadsheet you loaded did a "reset" on these they might be gone (poor programming by reputable company, I have seen it before: reset without the thought that a user may have customized.

Toolbars are stored in a file with the form *.xlb. In Win95 with XL97 it is in the c:windows folder. The name varies with versions, sometimes based on userID. If you have a backup of this file, you might be able to just copy the old one over the new one. I put a backup of this file somewhere near for when this happens.

Steve

[rdl@globalnet]

Thanks, Steve - I thought the toolbars were in the Book or Personal sheets, but obviously not! I have restored the .xlb file from a previous backup of my entire hard drive, and problem solved! Now I know which file is used, I'll keep an extra copy with my data backups. Thanks again.

[pieterse]

It is a good idea to attach a toolbar that is devoted to macros in one workbook to that workbook. That way the toolbar will always come back when your xlb file gets lost somehow. Here is my boilerplate on 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