: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.