Referencing Excel macros

I've got an Excel spreadsheet with various macros in VBA and a custom toolbar to access them.

The problem comes when different machines access the sheet since the toolbar buttons refer to the wrong location for the macros.

Is there a way to use an absolute reference for the buttons so that they point to the correct instance of the workbook??
Menthos1Asked:
Who is Participating?
 
ChrynobleCommented:
I have been frustrated by this myself. I did not find a solution to fix this, but I might have a work around.

In the workbook_Open sub you could add code that would change the action a toolbar button performed.

Private Sub Workbook_Open()
 Set myBar = Application.CommandBars("NPC")
 Set myControl = myBar.Controls("Start")
 With myControl
   .OnAction = "X:\Folders\Filename.xls!NameofMacro"
 End With
 myBar.Visible = True
End Sub

You might also be able to set the "X:\Folders\File.xls!MacroName" in the assign area of the customize tool. I have not had the greatest success with this.
0
 
Menthos1Author Commented:
Ok this sounds just what I need...

Have you had any success with tagging the macros onto a different sheet and then referencing them from there - I can forsee a problem with running the macros from an instance of the sheet.
0
 
Menthos1Author Commented:
Sorry, that last comment made no sense at all...

What I meant to say was won't there be a problem with Excel trying to open another instance of the same sheet to access the macros.
0
 
ChrynobleCommented:
Excel will look to open worksheets first. If Excel doesn't find the sheet there it will implicitly call the open command to open the workbook in question.

At least this is how it is suppose to work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.