Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
On Sep 12, 2:59*pm, BJ wrote:
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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
: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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Code To Run Always
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/agasear...in&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 -- Rick (MVP - Excel) "BJ" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
How to assign same code inside Option button code space ?? | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |