Need Code To Run Always

[BJ]

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.

 

[Rick Rothstein]

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.

 

[sameoldcalvin]

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_Noare
ActiveX OptionButtons.

Thanks for the help.

Sub Volume()
On Error GoTo Errhandler
If Sheets("Input").Range("Total_Vol").Value < 500 Then
PT_Yes.Enabled = False
PT_No.Enabled = False
PT_No.Value = True
Errhandler:
resume Volume
End Sub

 

[BJ]

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]

Do you have a range *named* Total_Vol?

 

[BJ]

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

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.

 

[BJ]

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]

I too am somewhat confused by the code working when you removed the "Not"...
if I understood everything you posted originally, I don't see why that is
the case. Other than not knowing why it works<g>, are you satisfied with the
solution you have now? If not, I think we would need to see all of your code
to see if some other part of the code is interacting in some way with what I
posted.

As for my appearing to never sleep... that is almost the case... I only
sleep on average about 4 to 5 hours a night. This leaves me plenty of time
to attend to my volunteer activities on these newsgroups as well as my
activities creating anagrams over in the

www.anagrammy.com/forum/index.html

website (although my activities there have slowed of late as I have been
concentrating a lot of time here in the Excel newsgroups in addition to
studying Excel to improve my postings here). If anyone is interested, you
can see more of my anagram creations here at this archive site ...

http://www.anagramgenius.com/agasearch.phtml?query=Rick+Rothstein&type=author

Be warned, however, there are some 2400 anagrams there and some of them are
downright raunchy. Anyway, in addition to those two activities, my lack of
sleep requirements also allows me the time necessary to keep up with several
TV shows (series) that I follow... and, of course, the various chores my
wife finds for me to do as well.<g>