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" <(E-Mail Removed)> wrote in
message news:25C70C85-68AE-4903-AB74-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
message news:66EDEB7B-0585-4A69-9134-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
message news:6BA5055D-0A5F-4E6B-9002-(E-Mail Removed)...
> 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 ***