Aim: To Loop Through Cells Containing Macro Names and Run Those Macros via Application.Run
Issue: The code used to loop through cells and run macros works perfectly only for macros without parameters. For those macros with parameters, it fails.
Problem in Detail:
I have stored a few macro names inside Excel cells.
I loop through those cells and call macros (names) written inside them as follows.
[The subroutine below is called from another subroutine by providing the parameters correctly.]
SelectAppsToRun(ctlGrpName As String, ws As Worksheet, activeTbx As MSForms.TextBox) Dim rng as Range For each rng in Sheet1.Range(“A1:A5”) Application.Run rng.value Next rng End Sub
It works correctly when there are no parameters for the macros. But generates error when it tries to run the following macro stored in a cell.
JumpToNextCtl, ws, ctlGrpName, activeTbx
This macro is supposed to take its parameters - ws, ctlGrpName and activeTbx - from the subroutine 'SelectAppsToRun'
ws as Worksheet, ctlGrpName as String, activeTbx As MSForms.TextBox
The error message I get is:
Cannot run the macro '"JumpToNextCtl", ws, ctlGrpName, activeTbx'. The macro may not be available in this workbook or all macros may be disabled.
I understand Application.Run considers the whole as a single string and the fact that there are commas in it does not treat them as separate parameters.
Is there any way to accomplish what I aim to accomplish?