Excel - Macro toolbar button error

[WMSTECH]

On a computer I'll call LocalHost, running OffXP on WinXP, I have a workbook with several macros attached. Their function is applying cell formatting. To make them accessible, I created a toolbar and made custom buttons with the macros attached.

This worked fine until LocalHost was needed temporarily by someone for a project. I shared out the workbook so it could be accessed on RemoteHost, also running XP software. With the workbook open on RemoteHost, I created the toolbar and buttons again on the remote Excel, attaching the macros, making sure I was using the ones from 'this workbook'.

It worked at first. Then the user would try a button and get a message that the workbook's XLS file can't be opened because it's already open--save the current one under another name before trying to open, etc. The buttons would work when the file was used at LocalHost but not at RemoteHost, and then after about a week, the buttons wouldn't work anywhere.

This doesn't appear to be a code problem--the macros can be run from the menu or with Alt-F8. That also indicates, I think, that Excel isn't looking in the wrong place for the macro code. I seem to have done something wrong in recreating the buttons on RemoteHost, but I can't figure out what.

Thanks in advance for any ideas!

 

[Dreamboat]

The problem COULD be the code after all.

The absolute best method of building custom toolbars/buttons/menus in Excel for "distributable" workbooks is to build them and remove them using the Workbook_Open or Workbook_New and Workbook_Close events.

See:

http://www.cpearson.com/excel/menus.htm#vba

Also, the Walk's gotta some cool tools for it (how to get the exact face you want on the buttons and an automated menu-maker). Find them at

http://www.j-walk.com

Anne Troy

http://www.VBAExpress.com

 

[WMSTECH]

Thanks, Dreamboat--I'll study this and see what I come up with!

--Bruce