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

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)



"bearly_competent" wrote in
message ...
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

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
--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

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

"bearly_competent" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

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

--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

"bearly_competent" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

"bearly_competent" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

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


"bearly_competent" wrote in
message ...
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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

"bearly_competent" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

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)



"bearly_competent" wrote in
message ...
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

"bearly_competent" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200775


"bearly_competent" wrote:

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

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?



*** Sent via Developersdex http://www.developersdex.com ***

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



*** Sent via Developersdex http://www.developersdex.com ***

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


On Wed, 09 Apr 2008 01:18:05 -0700, Bailsoft ** wrote:

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



*** Sent via Developersdex http://www.developersdex.com ***

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)



"Allen Marshall" wrote in message
...

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?



*** Sent via Developersdex http://www.developersdex.com ***