My problem was that I had an Excel toolbar that contained a number of customised buttons that I would like to make the default Excel toolbar for all users who log onto a shared PC. The macros behind the buttons manipulate data that is then used to automatically populate a third party software’s database. Basically, all the users of this PC need to have this toolbar present.
Now after a little bit of research I found that the file Excel10.xlb that is stored in the %userprofile%\Application Data\Microsoft\Excel directory contains any toolbar customisations ie) if you add a button, change the position of the embedded toolbars etc.
This file has a different name depending upon the Excel version and so is:
excel8.xlb for Excel 97
excel9.xlb for Excel 2000
excel10.xlb for Excel XP/2002
excel11.xlb for Excel 2003 and
excel12.xlb for Excel 2007
I created a central excel10.xlb file and a little batch file that sits in the C:\Documents and Settings\All Users\Start Menu\Programs\Startup group so that the excel10.xlb file is copied and overwrites any existing excel10.xlb file in the %userprofile%\Application Data\Microsoft\Excel directory when anyone logs on.
The trouble is, this only works for people who have logged into the PC before, made an amendment to a toolbar which in turn then creates a excel10.xlb file.
Any existing users who have never made a toolbar amendment or new users on the PC, do not load my version of the excel10.xlb file.
Excel obviously only looks for the excel10.xlb file when there has been a change to a toolbar. Even if an excel10.xlb file has been copied into the %userprofile%\Application Data\Microsoft\Excel directory, it will not be loaded by default unless a real change to a toolbar has been made in that profile.
Through a process of trial and error I managed to find the registry key that I needed. When I applied it to a profile that has never opened excel and that has my customised xlb file in place, it works.
The key is [HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options]
and the value is “MsoTbCust”=dword:00000008.
Applying it tells Excel to always use the xlb file regardless of whether any changes have been made to the toolbar.
I simply exported the key and have added into a batch file that merges this key into the registry each time a user logs onto the PC.