Solved: How to trigger macros from Validation List?

[pratster]

I'm trying to trigger one of three Macros based on the value of the dropdown selected from a validation list. But the Macros are not firing on using the Worksheet_Change event (which I have coded into the Worksheet module it is to be active in). Attached is the code I'm writing...where am I going wrong? B10 is the cell that has the Validation list

[mbarron]

Please don't post your code in as Word document. You should either post the code or, even better, as part a sample workbook. You can post your code by clicking the VBA icon above the text box. Then pasing your code between the tags.


Try this:
[vba] Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B10")) Is Nothing Then
Application.ScreenUpdating = False

Select Case Range("B10").Value

Case "Billback - % Rate"
Call Percentage_Rate_Macro
Case "Customer Agreement - % Rate"
Call Percentage_Rate_Macro
Case "Promotional OI - % Rate"
Call Percentage_Rate_Macro
Case "Billback - Value Rate"
Call Value_Rate_Macro
Case "Customer Agreement - Value Rate"
Call Value_Rate_Macro
Case "Promotional OI - Value Rate"
Call Value_Rate_Macro
Case "Billback Fixed"
Call Fixed_Rate_Macro
Case "Co-Op Advertising Fixed"
Call Fixed_Rate_Macro
Case "Cost Share Fixed"
Call Fixed_Rate_Macro
Case "Customer Agreement - Fixed"
Call Fixed_Rate_Macro
Case "Markdown - Fixed"
Call Fixed_Rate_Macro
Case "Scanback"
Call Fixed_Rate_Macro
Case "Pay for Space"
Call Fixed_Rate_Macro
Case "Trade Show Fixed"
Call Fixed_Rate_Macro

End Select
End If
Application.ScreenUpdating = True

End Sub

Sub Percentage_Rate_Macro()

Rows("11:11").Select
Selection.EntireRow.Hidden = True
Rows("12:12").Select
Selection.EntireRow.Hidden = False
Rows("13:13").Select
Selection.EntireRow.Hidden = True

Worksheets("Event").Range("B12").Activate

End Sub
Sub Value_Rate_Macro()

Rows("11:11").Select
Selection.EntireRow.Hidden = True
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("13:13").Select
Selection.EntireRow.Hidden = False

Worksheets("Event").Range("B13").Activate

End Sub
Sub Fixed_Rate_Macro()

Rows("11:11").Select
Selection.EntireRow.Hidden = False
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("13:13").Select
Selection.EntireRow.Hidden = True

Worksheets("Event").Range("B11").Activate

End Sub
[/vba]
This line will check to make sure the cell that was changed is B10. If it was B10 the rest of the macro will run.
[vba]If Not Intersect(Target, Range("B10")) Is Nothing Then[/vba]
This line, no matter what cell is changed results in your Case being a blank string
[vba]Select Case Target.Range("B10").Value[/vba]
You can use this change event to see what you were Selecting:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Range("B10").Value
End Sub[/vba]

[pratster]

Apologize for the word doc posting. Yes, your solution worked perfectly! Your extra lines for checking the cell and the msgbox are very helpful. Thank you very much.

[mdmackillop]

You can mark you post Solved using the Thread Tools dropdown.
Welcome to VBAX