Toolbar references to macros
Posted by Pete on January 14, 2002 6:16 AM
I have a spreadsheet that contains macros that are activated by buttons on a customised toolbar. I have attached the toolbar to the spreadsheet.
When the spreadsheet is copied and given a new name the buttons on the toolbar still point at the macros on the old spreadsheet. I would like to share the spreadsheet with others in my team and copies will be made.
The only way around this that I have found so far is to create another spreadsheet that only holds the macros and to make the buttons on the toolbar point to this new spreadsheet. However, this means that the macros spreadsheet is always running when the main spreadsheet is running.
Does anyone know of a sweeter way of dealing with this?
Posted by bob Umlas on January 14, 2002 6:50 AM
You can have the auto_open (or worksheet open event) run this code to redirect all the macros:
Sub RedirectTBs()
'example only
For Each tbb In CommandBars("Commandbarname").Controls
n = InStr(tbb.OnAction, "!")
tbb.OnAction = "'" & ThisWorkbook.FullName & "'" & Mid(tbb.OnAction, n)
Next
End Sub
Change the
"Commandbarname"
to be the actual name of the commandbar!
Posted by Pete on January 15, 2002 6:02 AM
I thank you for your assitance but would like to askyou please to explain a bit furhter as I have no Excel programming knowledge - I have successfully done what you say but once I have pasted it in I do not know how to save this and incorporate it into every worksheet that I use ???
