Get sheet Code Name and use to call Sub in sheet module

EdNerd

Active Member
Joined
May 19, 2011
Messages
470
I can call a sub in a worksheet module if I know the sheet's Code Name and use it in the call: Sheet1.DoHello
I can determine the worksheet I want and get its Code Name in another macro. But I haven't been able to figure out how to use that to call to the module and run a sub. Any help, please?
 
Why not move the VBA code out of the specific Sheet module in VBA, and into a General module in VBA.
Then it is easily available from any sheet in your workbook, and you don't need to preface the macro call with the sheet name.
 
Upvote 0
That would be wonderful. But I'm working within a "special" set of constraints: I've inherited workbook that only allows me access to the Sheet modules. So this appears to be my only hope at changing anything.
 
Upvote 0
Note that the General modules would not already be there, you need to insert them.

If you are unable to create/insert new General modules, can you create a new workbook, and copy all the data and code over (essentially creating a new copy of the workbook, structured like you want)?
 
Upvote 0
I have several dozen of these out there, scattered among many different users, most of whom understand little of Excel, none of whom understand VBA or the complexities of trying to "adjust" all of their files to work within whatever craziness corporate has decided to impose on our computer systems and network. I'm trying to avoid having to shepherd these people through a total rebuild of all their files just to add a few bits of functionality to the general modules, which are now off-limits to me. I can, however, still replace individual sheets, code included. I've been the total-rebuild route more than a few times in the 15+ years I've been doing this - adding or replacing a sheet of my choice is an easier way with less chance of blowing up somone's data.

Which is why I asked a simple question about one sheet calling a macro from another sheet. And only that.
 
Upvote 0
If you can "build" the name of the macro you want to call in your code and store it in a variable (like "MacroName"), you can use "Application.Run" to run the code, instead of "Call" (which does not work with variables), i.e.
VBA Code:
    Application.Run MacroName
 
Upvote 0
Solution
Thank you, Joe! That was exactly what I needed.
I built a String strM from the Sheet CodeName and the macro name.
Then used Application.Run strM. Worked great!!
 
Upvote 0

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