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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.