Hey Rick

Thanks for the update. However, I couldn't get it to work so I started
experimenting with the code. When I removed the "Not" from the Intersect
statement, it worked! I'm trying to understand the coding logic and it
doesn't really make sense to me that it is now working. Intuitively, if the
'intersect' of these cells/ranges, etc. is 'nothing' then run this code would
mean to me that nothing would happen unless the cells/ranges were all
'nothing.' So I am confused.

I really appreciate all of your help - this isn't the first dilemma you've
walked me through via the Group ...

One question - based on the time stamp of your reply - do you EVER sleep?
Or do you have a summer home in Transylvania by chance?

Thanks again for your coding wizardry.

BJ

"Rick Rothstein" wrote:

Well, actually, Excel does recognize the change, but not in the cell you are
examining... it sees the changes in the cells in the formula. So, you would
have to set up the Intersect function to look for those cells. For example,
assuming the formula is =F1+2*H2+3/J3, then you would have to test for F1,
H2 and J3 in the Intersect function...

If Not Intersect(Target, .Range("F1"), .Range("H2"), .Range("J3")) Is
Nothing Then

I'm not sure if the above will word wrap or not in your newsreader, but it
should all be on one line. If any of the cells in the formula are located on
a different worksheet, you wouldn't put them in the above statement; rather,
you would establish a Worksheet_Change procedure for that worksheet and test
for the cell's change there. Without testing, I think the rest of the code
should work fine.

--
Rick (MVP - Excel)


"BJ" wrote in message
...
:D ... Yes, I do ...

It is a single cell that contains a formula to sum three other cells and
based on my experimentation, therein lies the rub. When I change the
input
cells the Total_Vol range value changes, but Excel doesn't recognize an
actual change unless I select that cell (Total_Vol range) and hit Enter.

Is there a workaround or no? Thanks Rick.

BJ

"Rick Rothstein" wrote:

Do you have a range *named* Total_Vol?

--
Rick (MVP - Excel)


"BJ" wrote in message
...
Thanks Rick this is exactly what I was looking to do ... I am newbie
enough
to not know that a cell change can be an event as well. Thanks.

However, after copy/pasting the code ... nothing happens within the
worksheet. I'm not getting any error messages either which seems odd -
you'd
think one or the other would occur.

Any thoughts?

BJ

"Rick Rothstein" wrote:

You don't want that code to run all the time (that is why the
programming
world moved to event driven processing, to get away from the enormous
overhead continually running code entailed). Because your code does
something based on the value in the cell name Total_Vol, you would use
the
Change event procedure for the Input worksheet to monitor that value
for
you. You would use code something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Input")
If Not Intersect(Target, .Range("Total_Vol")) Is Nothing Then
If .Range("Total_Vol").Value < 500 Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
Else
PT_Yes.Enabled = True
PT_No.Enabled = True
PT_No.Value = False
End If
End If
End With
End Sub

and this code would be installed in the Input worksheet's code window.
If
you are not familiar with how to do that, follow this procedure...
right
click the Input worksheet's tab located at the bottom of the
worksheet,
select View Code from the popup menu that appears and copy/paste the
code
above into the code window that automatically opened when you did
that.
Okay, from now on, when you change the value in Range("Total_Vol"), it
will
set or unset the PT_Yes and PT_No controls according to the value
entered.

--
Rick (MVP - Excel)


"BJ" wrote in message
...
How do I get code to run all the time? That is, not when an event
occurs.
I
have the following code that I'd like to use ...

Sub Volume()
If Sheets("Input").Range("Total_Vol").Value < 500 Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
End Sub

Is it possible (a) to run the code - i.e. will the syntax work and
(b)
to
run the code as a constant check and not as an event? PT_Yes and
PT_No
are
ActiveX OptionButtons.

Thanks for the help.