Call macro from combobox selection

Question

I have added an activex combobox "ComboBox1" to my excel spreadsheet "Sheet1" and I want to be able to call different macros based on the selection in the combobox. Some of the macro names I have are "MC323", "MC616", "MC813".

So basically you select MC616 from the combobox list I want it to run the MC616 macro.

I have tried searching for answers but nothing has made since to me. Here is what I have so far, which isn't much and I'm sure isn't right anyway.

Option Explicit

Private Sub Workbook_Open()
With Sheet1.ComboBox1
ComboBox1.Clear
    .AddItem "MC323"
    .AddItem "MC616"
    .AddItem "MC813"
End With
End Sub

Sub ComoBox1_Change()
With Sheet1.ComboBox1
    Select Case ComboBox1.Value
        Case "MC323": MC323
        Case "MC616": MC616
        Case "MC813": MC813
    End Select
End With
End Sub

Sub MC323()
    Call MC323
End Sub

Sub MC616()
    Call MC616
End Sub

Sub MC813()
    Call MC813
End Sub

Answer 1

It all looks good until you get here:

Sub MC323()
    Call MC323
End Sub

Sub MC616()
    Call MC616
End Sub

Sub MC813()
    Call MC813
End Sub

Subroutine MC323 has only one line and that line says to call subroutine MC323 which has only one line and that line and that line says to call subroutine MC323 which has only one line and that line says to call subroutine MC323 which has only one line and that line says to call subroutine MC323 which has only one line and that line says to call subroutine MC323 which has only one line and that line says to call subroutine MC323 which has only one line and that line asays to call subroutine MC323.

So anyway, you get into a endless recursive loop, which is no good. Don't call subroutine MC323 inside of MC323. Put the code you want ran into the subroutine MC323. Like...

Sub MC323()
    MsgBox("Endless recursive loops are bad")
End Sub

Answer 2

I've been wondering why I couldn't get my examples to work - MC323, MC616 and MC813 are also worksheet cell references.

So..... ensure you have a sheet with a codename Sheet1 and an ActiveX combo-box called ComboBox1. In the ThisWorkbook module:

Private Sub Workbook_Open()

    With Sheet1
        .ComboBox1.Clear
        .ComboBox1.AddItem "MC323"
        .ComboBox1.AddItem "MyMacro2"
        .ComboBox1.AddItem "MyMacro3"
    End With

End Sub

Note the first item will cause an error - 'Cannot run the macro 'MC323'. The macro may not be available in this workbook or all macros may be disabled.

In a normal module:

Public Sub MC323()
    MsgBox "1"
End Sub

Public Sub MyMacro2()
    MsgBox "2"
End Sub

Public Sub MyMacro3()
    MsgBox "3"
End Sub

Finally, in the Worksheet module for Sheet1:

Private Sub ComboBox1_Change()
    Application.Run Sheet1.ComboBox1.Value
End Sub

Useful links to help files:

Application.Run Method

Ron de Bruin - How do I use Application.Run in Excel

Answer 3

You have a spelling error, that could be an issue, anyway... This should go into the Sheet1 module

Sub ComboBox1_Click()
    Select Case ComboBox1.Value
    Case Is = "MC323": MC323
    Case Is = "MC616": MC616
    Case Is = "MC813": MC813
    End Select
End Sub

This should go into a regular module,

Sub MC323()
    MsgBox "MC323"
End Sub

Sub MC616()
    MsgBox "MC616"
End Sub

Sub MC813()
    MsgBox "MC813"
End Sub

This is in the Workbook Module,

Private Sub Workbook_Open()
    With Sheet1.ComboBox1
        .Clear
        .AddItem "MC323"
        .AddItem "MC616"
        .AddItem "MC813"
    End With
End Sub