Excel VBA Command Button Click Event

[Jmckay1]

Hi,

I am very very new to excel VBA and would like your help with the following. I have a cell in a worksheet with a drop down menu that contains 8 different subjects. I want a command button to run a procedure when a subject is chosen from the cell drop down menu.

For example... if cell D13 in my worksheet = English, then run a procedure..... How do i get the code for the button click event to look at the subject in the worksheet cell D13???

 

[MARK858]

For a Command button it would be something like the code below where English, French and German are included in your dropdown...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()

    [color=darkblue]Select[/color] [color=darkblue]Case[/color] Range("D13").Value
    [color=darkblue]Case[/color] "English": aaa
    [color=darkblue]Case[/color] "French": bbb
    [color=darkblue]Case[/color] "German": ccc

    [color=darkblue]End[/color] [color=darkblue]Select[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

in a regular module put the subs below...

[color=darkblue]Sub[/color] aaa()
MsgBox "EEEE"
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[color=darkblue]Sub[/color] bbb()
MsgBox "FFFF"
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
Sub ccc()
MsgBox "GGGG"
[color=darkblue]End[/color] Sub

But I would think that you would be better off with a Worksheet change code.

Put this code in the Worksheet module...

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address = "$D$13" Then
    
        Select Case Target.Value

        Case "English": aaa
        Case "French": bbb
        Case "German": ccc

        End Select

    End If
End Sub

Obviously the subs in the regular module stay the same

 

[Jmckay1]

For a Command button it would be something like the code below where English, French and German are included in your dropdown...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()

    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Range("D13").Value
    [COLOR=darkblue]Case[/COLOR] "English": aaa
    [COLOR=darkblue]Case[/COLOR] "French": bbb
    [COLOR=darkblue]Case[/COLOR] "German": ccc

    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

in a regular module put the subs below...

[COLOR=darkblue]Sub[/COLOR] aaa()
MsgBox "EEEE"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[COLOR=darkblue]Sub[/COLOR] bbb()
MsgBox "FFFF"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Sub ccc()
MsgBox "GGGG"
[COLOR=darkblue]End[/COLOR] Sub

But I would think that you would be better off with a Worksheet change code.

Put this code in the Worksheet module...

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address = "$D$13" Then
    
        Select Case Target.Value

        Case "English": aaa
        Case "French": bbb
        Case "German": ccc

        End Select

    End If
End Sub

Obviously the subs in the regular module stay the same

Thank you for your help, I used the first example and this worked fine.