Posts by AvinEswar

The following are the links to my cross-posts.

http://www.vbaexpress.com/foru…de-an-Excel-Cell&p=409402

https://stackoverflow.com/ques…stored-inside-an-excel-ce

[xpost]

[/xpost]

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?

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.

Sub JumpToNextCtlfrmTxtBx(ActiveTxtBx As MSForms.TextBox)

    For i = LBound(objArray) To UBound(objArray)
        If objArray(i).Name = ActiveTxtBx.Name Then
            If i <> UBound(objArray) Then
                objArray(i + 1).Activate
            Else
                objArray(1).Activate
            End If
        End If
    Next i

End Sub

Sub JumpToNextCtlfrmComBx(ActiveComBx As MSForms.ComboBox)

    For i = LBound(objArray) To UBound(objArray)
        If objArray(i).Name = ActiveComBx.Name Then
            If i <> UBound(objArray) Then
                objArray(i + 1).Activate
            Else
                objArray(1).Activate
            End If
        End If
    Next i

End Sub

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

Option Explicit

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

Private Sub TxtCtl_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
        JumpToNextCtlfrmTxtBx TxtCtl

all subroutines we place here will run for the keydown event of all textboxes.

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

Option Explicit

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

Private Sub ComboCtl_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
        JumpToNextCtlfrmComBx ComboCtl

all subroutines we place here will run for keydown event of all comboboxes.

End Sub

STEP 4

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

Dim TxtBxArr() As New ClsTxtBx, ComboBxArr() As New ClsComBx

Private Sub Workbook_Open()

    Dim i As Integer, ole As OLEObject

    For Each ole In Sheet1.OLEObjects

          If ole.progID = "Forms.TextBox.1" Then

              i = i + 1
              ReDim Preserve TxtBxArr(1 To i)
              Set TxtBxArr(i).TxtCtl = ole.Object

          End If

          If ole.progID = "Forms.ComboBox.1" Then

              i = i + 1
              ReDim Preserve ComboBxArr(1 To i)
              Set ComboBxArr(i).ComboCtl = ole.Object

          End If

    Next ole

End Sub