PDA

View Full Version : Solved: How to trigger macros from Validation List?



pratster
11-06-2010, 06:37 PM
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
11-06-2010, 07:58 PM
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:
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

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.
If Not Intersect(Target, Range("B10")) Is Nothing Then
This line, no matter what cell is changed results in your Case being a blank string
Select Case Target.Range("B10").Value
You can use this change event to see what you were Selecting:
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Range("B10").Value
End Sub

pratster
11-07-2010, 04:41 AM
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
11-07-2010, 04:56 AM
You can mark you post Solved using the Thread Tools dropdown.
Welcome to VBAX