Application.Run to Activate Workbook and Worksheet

jrjobe

New Member
Joined
Feb 3, 2012
Messages
38
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
Hi all! Just as I was about to ask another question - I found the answer with regards to a variable workbook name. However, this led to another problem that if I can fix, I think what I am attempting to do is going to work great.

The macro "BACK" is used to check that the file name of another workbook is the correct name. This is stored in a Named Range "EstFileName" and when that workbook file name changes, I streamlined a way to update it. The macro (BACK) is also called using Application.Run in various other macros. What I am running into now, is how to update the code below to call the new macro "ActivateWBVariableName" while still allowing this macro to run and activate as it currently does.

VBA Code:
Sub BACK()
'
  Workbooks(Workbooks("DATABASE.XLSM").Worksheets("EstName").Range("EstFileName").VALUE).Activate
'
End Sub

The reason I am using the macro "ActivateWBVariableName" is so I only have one place to update the workbook "DATABASE.XLSM" name as I intended to update the name with the version number (such as database-v2.0.xlsm). Right now, I have a ton of macros that uses Workbook("DATABASE.XLSM").Activate that I am wanting to replace with:

Application.Run "ActivateWBVariableName" - however, the above macro is my current nemesis to get this all to work.

If this won't work, I'll abandon this process and go back to post my original question.

What are your thoughts on integrating Application.Run in the above code?

Thank you! Y'all have been great in pointing me in the right direction!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
On second thought - it just dawned on me that I may have to abandon all of this because I am using a customUI Ribbon menu in a .xlsx file that references the macros in the database.xlsm workbook. Unless someone has a way to map the menus to a variable workbook name, this won't work. Figuring out that menu took me a long time and it works very smoothly - but I don't recall ever coming across a way to use a variable name in the line below:

onAction="DATABASE.xlsm!Module1.MacroName" - replacing DATABASE.xlsm with something else that references a variable that stores the current workbook name that I had intended to change. Both the database.xlsm and the other .xlsx workbook work together, so both will always be open.

Mods - if you don't think any of this is possible - feel free to delete this thread as I won't be able to come to a complete solution.

Sorry everyone if you took a look at this and are trying to come up with a fix to the original post.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top