run a macro when cell value changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dropdown list that when a month is selected the month number (1 thru
12) updates cell w46. I found the below code in this discussion group but
when I change the month and cell w46 is updated with the number "2" the macro
does not run. I tested the macro seperately and it works fine. I plan to add
the "Case 3 to 12" after i get this to work. Can someone provide help? Is it
that after the user selects the month the VBA is not checking the cell? how
do I get the code to check for updates after the dropdown wis changed? thx,
Dan


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
If Target.Address = "$W$46" Then
Application.EnableEvents = False
With Target
Select Case .Value
Case 2: Macro17
End Select
End With
End If
ws_exit:
Application.EnableEvents = True

End Sub
 
I don't have an exact answer for you but I think has something to with
the routine name. I would try moving the routine from Sheet_Change to
Sheet_Selection_Change
 
the obvious answer it that the .value is not equal to 2.

You have to see why.

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
Dim l as Long
On Error GoTo ws_exit:
If Target.Address = "$W$46" Then
Application.EnableEvents = False
if isnumeric(target) then
l = clng(Target.Value)
Select Case l
Case 2: Macro17
End Select
else
msgbox "Bad Value ==>" & target.value & "<=="
end if
End If
ws_exit:
Application.EnableEvents = True

End Sub
 
Tom,
I did your suggested changes but could not get the msgbox to pop up. I test
the w46 field by having cell z46 perform a calculation based on the changing
value of w46. all calcs worked fine. My spreadsheet tab is called
"SetUp"...that is where the code is attached, if that helps? The macro17
jumps back and forth from "SetUp" to "Natural" tabs.
 
Back
Top