Executing a VBA Macro using a change in a data validation cell as the trigger

Hi everyone,

I would greatly appreciate your help to solve what should be a simple item but I have not found a solution in too many attempts, and now I think I am being moronic

I have a cell located at g28 that I have named "RevModSel"

There are 6 items in my data validation list with are:

• Aggressive
• Standard
• Conservative
• Roll Your Own
• Splash a Number
• Cell by Cell

The fundamental macro I built to test my functions is as follows:

Sub IF_Test()

Select Case Range("RevModSel")
Case "Aggressive"
Range("result") = 1
Case "Standard"
Range("result") = 2
Case "Conservative"
Range("result") = 3
Case "Roll Your Own"
Range("result") = 4
Case "Splash a Number"
Range("result") = 5
Case "Cell by Cell"
Range("result") = 6
End Select
End Sub

I have read many posts that indicate that I have to add something like the code below to trigger the macro on a change of the data validation result at "RevModSel" (cell G28)



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "RevModSel" Then
Call IF_Test
End If
End Sub

However, the debugger keeps failing an I am not seeing or understanding why.

Would someone please be so kind to modify the code above to help me get this working.

Thanks,

Hi Norie,

Nice try but I got a compile error: syntax error immediately turning this line yellow

Private Sub Worksheet_Change(ByVal Target As Range)

and the next line below is highlighted in blue

If Not Intersect(Target Range("RevModSel")) Is Nothing Then

Norie,

Here is the excel formula that works.

=IF(RevModSel=G22,1,IF(RevModSel=G23,2,IF(RevModSel=G24,3,IF(RevModSel=G25,4,IF(RevModSel=G26,5,IF(RevModSel=G27,6,7))))))


The macro should work the same way this formula does

Thanks Norie, Yup the comma made a big difference. Works perfect Now I want to figure out why I was such a Moron

Hi Norie,

I now have another question. The code below works fine in debug (F5), however my expectation was that when the user enters a number in cell "B5" (named Enter_Shift) that the code would execute. Can you please tell me why it is not executing?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Enter_Shift")) Is Nothing Then
Call checkInt
End If
End Sub


Sub checkInt()

If Range("Enter_Shift") > 0 And Range("Enter_Shift") < Range("Max_Shift") Then
MsgBox "OK"
Else
MsgBox "Specified Shift is out of Range"
End If

End Sub