Custom Toolbar Problems

[Thomas M.]

Excel 2003

I have created a custom toolbar in an Excel 2003 spreadsheet, and I've run
into two problems. I've tried googling both problems, but haven't had any
luck because, honestly, I'm not sure what to put in my Google query.

The Excel file is stored on a SharePoint site. The buttons on the custom
toolbar are all assigned to macros that are stored in the ThisWorkbook
module.

Problem 1
----------
If I make a change to the file and save it back to the SharePoint site, the
buttons on the custom toolbar stop working. When I click one of the buttons
I get a message showing the path to the document on the SharePoint site and
indicating that the macro cannot be found. Copying that path into the
button assignment does not fix the problem. If I go back in and assign the
button to the macro in the ThisWorkbook module, it will start working again
(until the next save back to the SharePoint site).

How do I make it so that the buttons on the custom toolbar always point to
the macros in the ThisWorkbook module so that I don't have to keep going
back and reassigning the buttons to their respective macros?

Problem 2
----------
I recently added two more buttons to the custom toolbar and saved the file
back to the SharePoint site. If I use my computer to open the file from the
SharePoint site I get those two new buttons on the custom toolbar, but if I
use a different computer to open the same file from the SharePoint site,
those two new buttons are missing.

How do I save the custom toolbar with the Excel file so that any changes to
the custom toolbar will be available from any computer?

--Tom

 

[Thomas M.]

I figured out problem 2. I went to Tools > Customize > Toolbars tab >
Attach, and then I removed the existing toolbar in the worksheet and
attached the new version. I'm still stumped on problem 1, thought.

--Tom

 

[Dave Peterson]

First, I don't have a suggestion on how to fix the problem--I've never used
SharePoint.

But you may want to dump the attached toolbar and create the toolbar when the
workbook opens.

Saved from a previous post:

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)