A client has upgraded his PC to XP and asked me to review and modify some Excel spreadsheets whose templates were created c. 1997 !
I rejigged the macros in VBA, but could not modify the command buttons on a toolbar, so I deleted hem and added my own to the same toolbar. Most of the code is in personal.xls, so that the functionality can be shared among a family of spreadsheets. However, after porting the new personal.xls and the revised templates to the client's machine, the old buttons were still visible.
I then deleted the old toolbar in its entirety and created a new toolbar (with a different name) and command buttons in personal.xls. I emailed the file to the client and instructed him to remove the original toolbar and add my new one (via views/toolbars). He reports that he cannot see my new toolbar on the list, though I can on my machine.
Where is the toolbar physically stored and how do I get it to my client ?
Toolbars are stored in *.xlb files. According to my Walkenbach book, for non network users this will be in Windows' main directory. The book also notes that a toolbar that's attached to a workbook automatically appears when the workbook is opened, unless the workspace already has a toolbar by the same name. Sounds like what you're experiencing.
Maybe your client needs a one-off deletion of the old toolbar so the new one that you sent with the workbook takes effect. I think this can be simply done from your clients machine in the VBE's immediate window, Application.CommandBars("The Toolbar Name").Delete
If is it not convenient to open the VBE from Excel on the client's machine, you could email a temporary file with that line of code to execute when the workbook opens & then close/delete that temporary file. Open the other file with the new toolbar and it should be OK.
Normally when using custom toolbars in a spreadsheet, standard practice is to (On Error Resume Next) delete the custom toolbar when the workbook opens (just in case it was already there), then create the toolbar on the fly, hide the toolbar whenever the workbook is deactivated, unhide when activated, delete when the workbook closes.
You have confirmed what I already suspected. As it happens the client has asked for a couple of amendments, so it is convenient to go and create the new toolbar on his machine. However, the idea of managing the toolbars through VBE is quite neat and I will keep it for future reference.