One of my clients needed a floating toolbar in an Excel worksheet for quick access to macros. Creating the toolbar was easy but trouble began when file names began to change.

The toolbar worked properly in a file called WORKA.XLS and a copy of the file was made as PROJECTA.XLS. The moment the toolbar button in PROJECTA.XLS was clicked, Excel opened WORKA.XLS and used it to store the relevant data. Whatever tactics I used to re-create the toolbar, Excel insisted on using WORKA.XLS for everything.

Next I changed the name of WORKA.XLS to WORKX.XLS. Opening PROJECTA.XLS and clicking the toolbar button generated an error along the lines of “cannot locate macro.” Excel was still trying to open and use WORKA.XLS.

After a bunch of research, including numerous references to PERSONAL.XLB files, I couldn’t find a solution to make the toolbar independent and portable between XLS files.

What finally worked was using Visual BASIC to dynamically create the toolbar. Not an elegant solution (nothing seems to be with Microsoft software) but this solution did the trick. Scroll down the page for the VB code.

No related posts.