How to force Microsoft Excel to use a custom toolbar for each user profile


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.

Advertisements

2 thoughts on “How to force Microsoft Excel to use a custom toolbar for each user profile

  1. Thanks! I was also going through the trial and error method of finding the right registry key / file combination to push a custom toolbar to all users. I had the registry narrowed down to 2 possible keys that could effect the toolbar. Know I know which one of the two I was looking at is correct.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s