Tool bar to follow spreadsheet

[Wes]

Hi,

I've written few a macros that I want available on a toolbar. I've made the
toolbar with the buttons and it works fine. I also used the code below to
make the toolbar visible when you open the file and to remove it when you
close it, this also works fine. The problem I have is that when I email the
file to someone, or even simply rename it, it gives an error when you click
the button. It can't find the file. I tried re-assigning the button and
noticed that some of my macros have the file name in single quotes and the !
before the macro name. How do I fix this so that the button looks for the
macro within the open file, instead of looking in the file location where I
saved it when I first made it?

Thanks in advance for your help.

Wes

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Views").Visible = False
End Sub

Private Sub Workbook_Open()
Application.CommandBars("Views").Visible = True
End Sub

 

[Ron de Bruin]

Hi Wes

Create your complete menu with code when you open the file and delete it when you close the file.
See the following pages

Debra Dalgleish's (Toolbar example from Dave Peterson)
http://www.contextures.com/xlToolbar02.html

Creating Custom Menus (John Walkenbach)
http://www.j-walk.com/ss/excel/tips/tip53.htm

Ole P. Erlandsen's Web Site (Example workbooks)
http://www.erlandsendata.no/english/index.php?d=endownloadcommandbars

How to customize menus and menu bars in Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Product=xlw