?
Solved

Referencing Excel macros

Posted on 2000-03-17
4
Medium Priority
?
203 Views
Last Modified: 2010-05-02
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??
0
Comment
Question by:Menthos1
  • 2
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
Chrynoble earned 150 total points
ID: 2631802
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
 

Author Comment

by:Menthos1
ID: 2643746
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
 

Author Comment

by:Menthos1
ID: 2643805
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
 
LVL 1

Expert Comment

by:Chrynoble
ID: 2650210
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question