macro to run when cell changes value

[cmefly]

Hi,

I've got a drop-down menu (using forms not a listbox) in A1. In B1, i've got a vlookup formula that is dependent on A1. So when A1 changes, so does the value in B1.

I need a macro that is triggered only when B1 changes.

In my case, B1 can be x, y, or z. Each one would trigger a different macro.

I've got this but it doesn't work...any ideas?


Private Sub Worksheet_Change(ByVal Target As Range)
Target = Range("b1")
If Target.Value = "x" Then
MACRO1
End If

If Target.Value = "y" Then
MACRO2
End If

If Target.Value = "z" Then
MACRO3
End If
End Sub

 

[taurean]

You were very close to making it work. Change it a bit like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Select Case Target.Value
Case "x"
MACRO1
Case "y"
MACRO2
Case "z"
MACRO3
End Select
End Sub

 

[cmefly]

doesn't seem to be calling the macro....

i inserted an "end if" after "end select"

 

[cmefly]

b1 changes based on A1...

should the target value be switched?

 

[cmefly]

bump...please help...macros aren't being triggered...

 

[taurean]

Apologies. My mistake. I did not read about form. You'd be better of with another event like:

Private Sub Worksheet_Calculate()
Select Case Range("B1").Value
Case "x"
MACRO1
Case "y"
MACRO2
Case "z"
MACRO3
End Select
End Sub

 

[cmefly]

okay...so now it works but it keeps looping my macro....i have to use control-break to exit....

 

[taurean]

At this point, try this (we'll stop event triggers when the code runs!)

Private Sub Worksheet_Calculate()
[COLOR=blue]Application.EnableEvents = False[/COLOR]
Select Case Range("B1").Value
Case "x"
MsgBox Range("B1").Value
Case "y"
MsgBox Range("B1").Value
Case "z"
MsgBox Range("B1").Value
End Select
[COLOR=blue]Application.EnableEvents = True
[/COLOR]End Sub

 

[cmefly]

HELP!!!

for some reason the above PRIVATE macro is being applied to other sheets!!!!

 

[taurean]

By any chance are you using the following:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
End Sub

In other case, does this sheet (the sheet in question) have links with other sheets which when updated will cause some values to change and thus will cause the event to occur?