How To Run Macro From Selection In A Dropdown List

[aedctalk]

Hi! So I have a validation dropdown list with Quarter 1, Quarter 2, Quarter 3, Quarter 4, in Configuration!J15..

I want it so that when they select each one.. it'll run a macro for each.

So under my workbook vba I have

Sub CandyOrder()

If (Sheets("Configuration").Range("J15").Value = "Quarter 1") Then

UnProtectAllSheets
CandyQuarter1
ProtectAllSheets

ElseIf (Sheets("Configuration").Range("J15").Value = "Quarter 2") Then

UnProtectAllSheets
CandyQuarter2
ProtectAllSheets

ElseIf (Sheets("Configuration").Range("J15").Value = "Quarter 3") Then

UnProtectAllSheets
CandyQuarter3
ProtectAllSheets

ElseIf (Sheets("Configuration").Range("J15").Value = "Quarter 4") Then

UnProtectAllSheets
CandyQuarter4
ProtectAllSheets

End If
End If
End If
End If

However, when i sleect any one of those in the dropdown they dont run the macros within each IF statement.... just nothing happens.

What am i doing wrong please?

Thank you so much for any help

 

[ChrisM]

Your code must be tied to an event in order for it to run. As you have it written, your:

Sub Candyorder()

will just sit waiting for code to call it.

Try the following code placed in the sheet module you are working in:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$15" Then
    UnProtectAllSheets
            Select Case Sheets("Configuration").Range("J15").Value
                  Case "Quarter 1"
                           CandyQuarter1
                  Case "Quarter 2"
                           CandyQuarter2
                  Case "Quarter 3"
                           CandyQuarter3
                  Case "Quarter 4"
                           CandyQuarter4
            End Select
     ProtectAllSheets
End If

End Sub