Posts by AvinEswar

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    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.]



    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?

    I know this is a very very late response. But I wish it helps someone who stumbles upon this page with the same frustration.


    Don't be put off by the length of what is written here. Not complicated like it appears. Simple to understand with a bit of patience. And it will save tons of time.


    Solution: The way is to create two Class Modules (one for TextBox KeyDown event and one for ComboBox KeyDown event) and set all textboxes and comboboxes to these classes. The class module will pass the name of active oleObject to the subroutine you wish to run.


    Also like Jonathon required, we could write a subroutine inside the keydown event of these class modules just once and that would apply to all textboxes and comboboxes in the worksheet. This saves the time of writing the same codes for KeyDown event of all textboxes and comboboxes separately.


    The above classes would determine which oleObject is active and pass that to the subroutine you want to run using that information.


    Solution Step by Step


    STEP 1


    I will first write the subroutine that finally runs - the one that controls tabbing between textboxes and comboboxes using the information about the active oleObject.


    Assumed that array is already in place.


    Here we loop through the array to find the position of active oleObject in the array and jump to the next oleObject in the array. The name of the active oleObject is passed as parameter to this subroutine by the class modules which we create.


    STEP 2


    Create a Class Module for TextBox (Insert > Class Module) .


    I am naming it as ClsTxtBx (Click on the created Class, press F4 and type the name in the box to the right of 'Name' in the new Properties window that pops up if you do not have one already.)


    Inside the ClsTxtBx class module, type the following codes


    Code
    1. Option Explicit
    2. Public WithEvents TxtCtl As MSForms.TextBox


    Now select TxtCtl from the dropdown list on the left top of the module.


    Select the KeyDown event for the TxtCtl from the dropdown list on the right top of the module.


    Type the following codes for the KeyDown event


    Code
    1. Private Sub TxtCtl_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    2. JumpToNextCtlfrmTxtBx TxtCtl
    3. all subroutines we place here will run for the keydown event of all textboxes.
    4. End Sub


    STEP 3


    Create a Class Module for ComboBox (Insert > Class Module) .


    I am naming it as ClsComBx (Click on the created Class, press F4 and type the name in the box to the right of 'Name' in the new Properties window that pops up if you do not have one already.)


    Inside the ClsComBx class module, type the following codes


    Code
    1. Option Explicit
    2. Public WithEvents ComboCtl As MSForms.ComboBox


    Now select ComboCtl from the dropdown list on the left top of the module.


    Select the KeyDown event for the TxtCtl from the dropdown list on the right top of the module.


    Type the following codes for the KeyDown event


    Code
    1. Private Sub ComboCtl_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    2. JumpToNextCtlfrmComBx ComboCtl
    3. all subroutines we place here will run for keydown event of all comboboxes.
    4. End Sub


    STEP 4


    Place the following codes in the Open event of the workbook.