CODEWITHSUNDEEP
excelvba
Max Murrell
Max Murrell

Reputation: 63

Using drop down list linked to macros

This is my first time using a drop down list. I was wondering if there was a way to assign a macro to each of the items in the drop down list.

enter image description here

For an example if I selected BZ1A I would want it to run the sub I have called BZ1A.

Upvotes: 0

Views: 1156

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Run Macros From Drop Down

  • Copy the first code into the sheet module of the worksheet containing the drop down, e.g. Sheet1 (the name in parentheses in the VBE Project Explorer).
  • Adjust the values in the constants section.
  • Put your codes into the same module, e.g. Module1. Otherwise you will have to modify the code.
  • In this example the drop down list is in cell A1 of worksheet Sheet1 and contains the list (values) Sub1, Sub2, Sub3.

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const CellAddress As String = "A1"
    Const ModuleName As String = "Module1"
    If Target.Cells.CountLarge = 1 Then
        If Not Intersect(Range(CellAddress), Target) Is Nothing Then
            Application.EnableEvents = False
            On Error GoTo clearError
            Application.Run ModuleName & "." & Target.Value
            Application.EnableEvents = True
        End If
    End If
    Exit Sub
clearError:
    MsgBox "Run-time error '" & Err.Number & "': " & Err.Description
    Resume Next
End Sub

Standard Module e.g. Module1 (Example)

Option Explicit

Sub Sub1()
    MsgBox "Running 'Sub1'"
End Sub
Sub Sub2()
    MsgBox "Running 'Sub2'"
End Sub
Sub Sub3()
    MsgBox "Running 'Sub3'"
End Sub

Upvotes: 1

Related Questions