Run macros from multiple drop down lists in same sheet

[MAJ0RMAJ0R75]

Hi there,

I've been searching the net for a solution to my problem, however given my lack of understanding of VBA, have not been able to decipher solutions to similar problems previously posted.

I have created a form in Excel, which contains a number of drop down lists. I have also saved a number of macros which I would like to activate upon specific drop down selections being made. I have been able to get a set of macros to work on one drop down by using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$17" Then
If Target = "Ex Factory" Then
Exf
ElseIf Target = "" Then
noexf
ElseIf Target = "Delivered" Then
noexf
ElseIf Target = "Delivered & Installed" Then
noexf
End If
End If
End Sub

I have read however that you can only use one instance of Private Sub Worksheet_Change(ByVal Target As Range) per worksheet.

I'd like to combine multiple instances of this code to different drop downs, for example adding the code below to the one above:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
If Target = "Contract Review" Then
CR
ElseIf Target = "" Then
BR
ElseIf Target = "Bid Review" Then
BR
End If
End If
End Sub

I have read that the CASE statement can do this, but cannot work out how to translate what I want to achieve into the code.

Hopefully someone out there can offer a solution.

Many thanks in anticipation.

 

[Trebor76]

Hi MAJORMAJOR75,

Welcome to the forum!!

Yes, you're right as it wouldn't make sense to have more than one WorkSheet_Change event on a single sheet.

That said you can capture any cell change you wish and execute some code on it, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Address
        Case Is = "$C$3"
            Select Case Target.Value
                Case "", "Bid Review"
                    Call BR
                Case "Contract Review"
                    Call CR
            End Select
        Case Is = "$C$17"
            Select Case Target.Value
                Case "", "Delivered", "Delivered & Installed"
                    Call noexf
                Case "Ex Factory"
                    Call Exf
            End Select
    End Select
    
End Sub

Regards,

Robert

 

[MAJ0RMAJ0R75]

Briliant, fantastic, simple.

Many thanks Robert for your concise and swift response.

 

[Trebor76]

Thanks for the feedback and you're welcome