Attaching Custom Toolbars to Workbooks

[Tony Turenne]

I have created a custom toolbar for a project. I have attached it to the
worksheet, but when I open the file on another computer, it opens the
original version of the toolbar that I created. The modifications and
additions that I made to the toolbar after the original was created are
lost. I have tried removing the toolbar from the worksheet, saving and
quitting Excel, reopening and then adding the toolbar. This does not work.
Only the original version of the toolbar shows up on other computers. It
continues to work fine on my computer.

Note: This is not the original version of this workbook. I have several
saved versions in much less complete form. I used "save as" from the file
menu to do this as it seems this works better at preserving the
functionality of the numerous macros I have the workbook.

Any suggestions?

 

[Tony Turenne]

Problem solved. On the second computer, I had previously opened an earlier
version of this file, so this Excel Application had saved the original
version of the custom toolbar. Since the custom toolbar still had the same
name, Excel did not realize that I had modified it and so it did not update
the toolbar. To compensate, I opened this version of Excel (without opening
my workbook) and deleted the custom toolbar. Then I reopened the workbook
with the custom toolbar attached to it. The updated version of the custom
toolbar was then loaded into this version of Excel and everything is now
working properly.

 

[JE McGimpsey]

Problem solved. On the second computer, I had previously opened an earlier
version of this file, so this Excel Application had saved the original
version of the custom toolbar. Since the custom toolbar still had the same
name, Excel did not realize that I had modified it and so it did not update
the toolbar. To compensate, I opened this version of Excel (without opening
my workbook) and deleted the custom toolbar. Then I reopened the workbook
with the custom toolbar attached to it. The updated version of the custom
toolbar was then loaded into this version of Excel and everything is now
working properly.

Couple of techniques I use in my applications:

1) Almost all my toolbars are built using VBA, so the first part of the
toolbar building code deletes any previous version:

On Error Resume Next
CommandBars("JEM123").Delete
On Error GoTo ErrHandler
With CommandBars.Add(Name:="JEM123", _
Position:=msoBarTop, Temporary:=True)
'code to build toolbar here
End With

2) Note that in the above code, the toolbar is established as Temporary,
so it's automatically deleted when XL is closed. I still explicitly
delete it on startup in case XL shut down irregularly.

3) If I can't for some reason, build the commandbar in code, I've
attached all my toolbars to an add-in, then in my main application
workbook, I delete any versions of the toolbar prior to installing the
add-in. I then use code to uninstall the add-in and delete the toolbars
prior to closing the application workbook.