Use a variable for subroutine name

I have many macros in a workbook but only 7 or 8 are normally used. Sometimes the particular macro name needed is lost in this list of macros. I added a worksheet (named "DOC") which lists the most common macros with a brief description of their function. Now I'd like to highlight the macro name I want and call it. Although the following code doesn't do this, it has the same problem. How do I fix it?

The code asks the user for a macro name and then attempts to call that macro. It gets a compile error because the variable name for the macro isn't allowed. If I can get this to work, I can solve the real problem.

Sub VarMac()

Dim macName as String

macName = InputBox("Please enter the macro name.")

MsgBox macName

Call macName

End

The key word "Private" keeps Procedures from being listed in the Macro list

Code:

---

Private Sub Blahblah()
Private Function Blehbleh()

---

The Module directive "Option Private Module" keeps all Procedures in that Module from being listed

Code:

---

Sub VarMac()
Dim macName As String
macName = InputBox("Please enter the macro name.")
MsgBox macName
Application.Run macName
End Sub

---

Quote:

---

Originally Posted by jwise

I have many macros in a workbook but only 7 or 8 are normally used. Sometimes the particular macro name needed is lost in this list of macros. I added a worksheet (named "DOC") which lists the most common macros with a brief description of their function. Now I'd like to highlight the macro name I want and call it. Although the following code doesn't do this, it has the same problem. How do I fix it?

The code asks the user for a macro name and then attempts to call that macro. It gets a compile error because the variable name for the macro isn't allowed. If I can get this to work, I can solve the real problem.

---

I'd add a 'Control Panel' worksheet with 7-8 buttons each calling the appropriate macro (or an UserForm if I wanted to get fancy)

It doesn't seen very user friendly to make people type in the name of a macro to run.

Thank you for all the replies. VBAX is a wonderful site and resource.

"Application.Run" was exactly what I needed. I've tested the sample code and it performed as desired.

You overlooked the very sensible comments Paul_Hossler made.

I appreciate every reply since the responder is taking his/her time to help me. I did not overlook any reply; I made a value judgement that the improved function was not worth the time to code and test. I decided it wasn't since the code was really for my use only. Had this been code that I would have sold or provided to others, the decision would have probably been different. Thanks for your input.