CODEWITHSUNDEEP
vbaexcelwildcard
Dm3k1
Dm3k1

Reputation: 187

Call Macro by wildcard in cell in Excel VBA

I currently have a combo box in Excel that is assigned to a "Macro List" on a seperate worksheet that has approximately 200 various macros listed. It can be sometimes combersome to search the dropdown to get the macro you want to select (they are in numerical order, so its not too bad) but I think this could be better.

Most of the macros are structured in this way "PA1111_Name" - what I would like is to allow a user to type in just 1111 in a cell and press a "run" button that directs to the above macro. In SQL it would be something like this:

SELECT Macro FROM Module WHERE Macro Like '*' & Cell.A2 & '*'

These numbers are unique so I'm not concerned with the potential for grabbing multiple macros.

Thank you!

Upvotes: 4

Views: 724

Answers (1)

user2140261
user2140261

Reputation: 7993

The following will go through all macros inside the Mapping Module of the Active VBProject and test to see if the Name contains the Value found in A1, If it does it will run that Macro if none are found it displays No Macro's Found Matching Entered Value.

Remember this will only run the first macro it finds with the Value contained in it, as i assumed you didn't have duplicate values in macro names.

Sub RunMacroContainingValue()
Dim cpCurrent As VBComponent
Dim lngCurrentLine As Long
Dim SubName As String
For Each cpCurrent In Application.VBE.ActiveVBProject.VBComponents
    If cpCurrent.Name = "Mapping" Then
        With cpCurrent.CodeModule
            lngCurrentLine = .CountOfDeclarationLines + 1
            Do Until lngCurrentLine >= .CountOfLines
                SubName = .ProcOfLine(lngCurrentLine, 0)
                If InStr(SubName, [A1]) > 0 Then
                    Application.Run SubName
                    Exit Sub
                End If
                lngCurrentLine = .ProcStartLine(SubName, 0) + _
                .ProcCountLines(SubName, 0) + 1
            Loop
        End With
    End If
Next cpCurrent
MsgBox "No Values Found Matching Value"
End Sub

Upvotes: 2

Related Questions