VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell

[PachAvin]

Remembering: Tell us WHAT you want to do, not HOW you think you want to do it


I will try my best to explain in detail what I really want to do rather than detailing on my ideas.


I have a few sheets containing many shapes and ActiveX controls (file attached exhibiting simplified version of my problem). Selected Shapes and ActiveX controls have been grouped together (with groups being given a name). There are few such groups.


The ActiveX controls are assigned to Classes with KeyDown event at the Open event of workbook. One class for dealing with TextBoxes and another class for dealing with ComboBoxes. So when I press TAB while inside an ActiveX control, the cursor moves to the next ActiveX control in that group. The macro that facilitates this is JumpToNextCtl ws, ctlGrpName, txtctl


With respect to each group of ActiveX Controls / Shapes, separate macros are also supposed to run on pressing TAB like mentioned above. Taking examples of the attached file:
(1) For the currently active ActiveX control, If its sheet is Sheet1 and ActiveX group name is AddContGrp the macro that will run is DisplayContactsGroupSheetName.
(2) For the currently active ActiveX control, If its sheet is Sheet2 and ActiveX group name is AddProjGrp the macro that will run is DisplayProjectGroupSheetName.


I have given all these macro names in a sheet named 'Tables Sheet' in a table. To loop through these table range and pick the macro to run, I call the following macro inside the Class Module mentioned earlier.


SelectMacrosToRun ws, ctlGrpName


This macro finds the worksheet name (of currently active ActiveX control) in the first column of the table and ActiveX controls group name (of currently active ActiveX control) in the second column of the table. If both match it will run the macro in the third column of the table.


I could have directly called those macros in the KeyDown event of the classes. But avoided doing that for the following reasons:


(1) All macros will run whenever I press the TAB. I would like only those macros to run that are related to the currently active ActiveX Control.
(2) Want to keep Class Module compact without filling in all the macro names. Instead I prefer to keep those macro names in a separate sheet, choose from there according to the currently active ActiveX control and then run only those particular macros.
(3) Keeping the macro names in a separate sheet enables me easily narrow down into the problematic macros because beside the macro names in the sheet, there will also be the names of Worksheet and ActiveX Control Group names. So when I press TAB when inside an ActiveX control, I can easily refer to the sheet and check just the macros related to that ActiveX control.


For the time being, I am calling the macro [ JumpToNextCtl ws, ctlGrpName, txtctl ] directly from the Class Modules.

But what I want is, I want to choose this macro from the table and run using [ SelectMacrosToRun ws, ctlGrpName ]