Call a Sub with a String - VBA

Question

I'm trying to build my code that calls a Sub with a String based on answers to this question Trying to call a Sub with a String - VBA by using Application.Run or CallByName but I'm getting:

Run-time error '1004': Application-defined or object-defined error

when I try Application.Run

and

Run-time error '438': Object doesn't supprot this property or method

when I try CallByName.

Calling as well called Subs are in the same sheet Sheets("Rozpad_hodnocení").

I'm trying to go through all option buttons with index number in name on that sheet to find Obuttons with "ne" in name, which works. With this found index I want to call a sub with the same index number and "ne" in name. Called subs are like this:

OB9Ne_Click()
OB10Ne_Click()
OB11Ne_Click()

I can't move my code to form or module

Here's sample of a code:

Sub adsf()
    Dim s As Shape
    Dim Pro As String
    For Each s In Sheets("Rozpad_hodnocení").Shapes
        If s.Type = 12 And s.Name Like "*Ne" Then
            Pro = s.Name + "_Click()"
            Application.Run Pro
            'CallByName List3, Pro, VbMethod
            'CallByName Sheets("Rozpad_hodnocení"), Pro, VbMethod
        End If
    Next
End Sub

Maybe the answer is allready in previous question, but I didn't manage to adjust my code to it.

If it's a sheet module, you will have to use CallByName try removing the parentheses from the Pro variable. — SierraOscar

Jakub Smejkal Jakub Smejkal · Accepted Answer · 2016-03-31T12:14:21

Thanks to @Kyle I changed Subs to Public, @Macro Man suggested removing the parentheses from Pro and with combination of these it works.

Call a Sub with a String - VBA

1 Answers