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

[EdNerd]

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?

 

[Joe4]

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.

 

[EdNerd]

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.

 

[Joe4]

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)?

 

[EdNerd]

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.

 

[Joe4]

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.

    Application.Run MacroName

 

[EdNerd]

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!!

 

[Joe4]

You are welcome.
Glad I was able to help!