cell update doesn't run macro

[bearly_competent]

I've searched thru lots of posts, and I can't understand why only I seem to
have an issue with starting a macro based on a cell change. This code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "B3" Then
Call volatility_rate
End If
End Sub
-doesn't do squat, whether I change the cell thru the drop-down or manually.
Why not? I have this coded in the worksheet I want it for, and nothing
happens.

Thanks,
-Dave

 

[Bob Phillips]

Are events enabled.

Type

Application.EnableEvents = true

in the immediate window

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary''s Student]

You need dollars:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox (Target.Address)
If Target.Address = "$B$3" Then
Call volatility_rate
End If
End Sub

 

[bearly_competent]

Nope; I tried both suggestions. Gary's just opens up a msg box, which I
didn't need (just using the dollar signs didn't do the trick either), and
Bob's events code didn't work. I tried putting it in both the worksheet and
module(macro) code.

 

[bearly_competent]

After fiddling around some more, I see that Gary's dollar actually were the
problem, but the macro only runs if I manually change the cell. How can I get
it to work thru updating with the drop-down I have there?

Thanks, guys

 

[Gary''s Student]

It SHOULD work with a data validation drop-down. Maybe the problem is in the
called routine. Just for fun try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Application.EnableEvents = False
Call volatility_rate
Application.EnableEvents = True
End If
End Sub

 

[bearly_competent]

Turns out it wasn't just for fun Not only didn't it help, but it no
longer works, even after I went back to the previous code (w/o the events
code). I tried Application.EnableEvents as true and false, before and after
the code, but I can't get even manual changes to call the macro, like they
were doing before. What's going on?

 

[Rick Rothstein \(MVP - VB\)]

Maybe your code exited before events were turned back on. Execute this
line...

Application.EnableEvents = True

in the Immediate window and see if that returns your code to (not) working
the way it used to (not) work.

Rick

 

[Bob Phillips]

Have a cell linked to the DV cell, =G1 for example, and then use the
Worksheet_Calculate event to trap the change.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Allen Marshall]

I am having a similar problem. When I step into the macro I am trying
to run (subroutine) the value of Target is not a bunch of cells hence a
range object, but the actual value that has just been entered into the
range. Earlier today, however, it was sending the range value. I would
have expected that this event would always get the range object, not the
value. Any insights?

 

[Bailsoft]

Hi,
I'm having the same problem, but only with Excel 2003 (at work). At home
I use Excel 2000 and on this earlier version the Worksheet_Change code
works as expected after a Validation drop down event.
Regards,
Bailsoft

 

[Gord Dibben]

I run Excel 2003 and this code works every time for me when I select an item
from the DV dropdown list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Application.EnableEvents = False
Call My_Macro
Application.EnableEvents = True
End If
End Sub

Post your exact code and is B3 the DV dropdown cell?


Gord Dibben MS Excel MVP

 

[Bob Phillips]

It would, but Value is the range default property, so if there is only one
cell it will show that cell value.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)