Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
Is there code that I can put in a worksheet module that automatically stops
all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
I'm assuming you have the time portion set to a variable that is accessible
to the Worksheet's Code Module. You have one procedure that schedules the next time another procedure within the module is ran. You then set the Deactivate Event within the Worksheet's Code Module ("Worksheet" in the left combo-box and "Deactivate" in the right combo-box just above the code module) to run the command that unschedules the next run time. Example code line within the Deactivate Event Application.OnTime m_dteNextRunTime, "pcdReoccuringProcedure", , False To get it back going again when the worksheet becomes active again, you just use the Activate Event, set the Date/Time variable accordingly, and then use the OnTime method to get it a going again. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "dgold82" wrote in message ... Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
Take a look at Chip Pearson's notes:
http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
Yeah, you right. However, he did mention of a timer macro, which is what
had me think of the OnTime command. However, if the count down is a recurrsive looping, he could have a boolean variable that is accessible to the code that is "running" as well as accessible to the Worksheet's Code Module. Within the Worksheet's Activate Event, this boolean would be set to True, and have the worksheet's Deactivate Event set this same boolean to False. The procedure that is supposedly running, each time it reaches a certain point within the recursive code, it would check to see this boolean variable is false, and if it is false, then the procedure would just end. I know it's not a hard code break out of the event, but it's a way around the situation to be done programmatically. If one is truly using OOP by using Class Modules along with setting up and utilizing not only the properties and methods, but also the events, that can get to be a bit more tricky and one would have to do a thorough understanding how to setup the code to break out of it, though still use the same basic concept as I stated above for procedure based coding. OOP based coding is more difficult to learn than procedure based coding (Not sure if there is really a industry proper term for this type of coding that's not OOP based), but OOP based coding also a lot more powerful as you can do more things with it. Where OOP's biggest power comes from as opposed to procedure based coding, it's in the use of events that procedure based coding for the most part lacks. Yes, mimicking can take place, but very combersome to do. One such example I have used within the userforms is the snippet code below: Do Until l_bolCheck = False Or g_strCurrentReasonCode < "" l_frmIssue.Show vbModeless Do Until l_frmIssue.Visible = False DoEvents Loop If g_strCurrentReasonCode = "" Then If MsgBox(l_strMessage, vbCritical + vbYesNo, "Reporting Error") = vbNo Then l_bolCheck = False End If Else m_objCurrentWorkOrder.fncRecordStatusReason l_StatusCode, g_strCurrentReasonCode pcdUpdateForm End If Loop -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Dave Peterson" wrote in message ... Take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
I tried this and it sort of worked to halt code... On a sheet I added a
command button with the following code... Dim lng As Long For lng = 1 To 10000000 DoEvents Application.StatusBar = lng Next lng Application.StatusBar = False Then in the Deactivate event of thisworkbook I added Private Sub Workbook_Deactivate() End End Sub if you switch sheets with either Ctrl+Tab Window | Select another sheet It executes the End and halts all code. Interestingly you can not just click on the sheet in the Task Bar... Of couse End is generally speaking a bad idea and I do not endorse the use of this code but I wanted to prove that I'm smart... Best I could manage was to sort of prove it so I guess I'm sorta smart. ;-) -- HTH... Jim Thomlinson "Dave Peterson" wrote: Take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
On Jul 30, 4:07*pm, "Ronald R. Dodge, Jr."
wrote: Yeah, you right. *However, he did mention of a timer macro, which is what had me think of the OnTime command. However, if the count down is a recurrsive looping, he could have a boolean variable that is accessible to the code that is "running" as well as accessible to the Worksheet's Code Module. *Within the Worksheet's Activate Event, this boolean would be set to True, and have the worksheet's Deactivate Event set this same boolean to False. *The procedure that is supposedly running, each time it reaches a certain point within the recursive code, it would check to see this boolean variable is false, and if it is false, then the procedure would just end. *I know it's not a hard code break out of the event, but it's a way around the situation to be done programmatically. If one is truly using OOP by using Class Modules along with setting up and utilizing not only the properties and methods, but also the events, that can get to be a bit more tricky and one would have to do a thorough understanding how to setup the code to break out of it, though still use the same basic concept as I stated above for procedure based coding. OOP based coding is more difficult to learn than procedure based coding (Not sure if there is really a industry proper term for this type of coding that's not OOP based), but OOP based coding also a lot more powerful as you can do more things with it. *Where OOP's biggest power comes from as opposed to procedure based coding, it's in the use of events that procedure based coding for the most part lacks. *Yes, mimicking can take place, but very combersome to do. One such example I have used within the userforms is the snippet code below: * * Do Until l_bolCheck = False Or g_strCurrentReasonCode < "" * * * * l_frmIssue.Show vbModeless * * * * Do Until l_frmIssue.Visible = False * * * * * * DoEvents * * * * Loop * * * * If g_strCurrentReasonCode = "" Then * * * * * * If MsgBox(l_strMessage, vbCritical + vbYesNo, "Reporting Error") = vbNo Then * * * * * * * * l_bolCheck = False * * * * * * End If * * * * Else * * * * * * m_objCurrentWorkOrder.fncRecordStatusReason l_StatusCode, g_strCurrentReasonCode * * * * * * pcdUpdateForm * * * * End If * * Loop *-- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000"Dave Peterson" wrote in message ... Take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. *(I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson Maybe this is too simple for what the OP is trying to accomplish--but something like the code below seems like it would accomplish what he's trying to do. It assumes that while his timer macro runs, it is not changing the activesheet. Sub testexit() Dim t As Date Dim s As String Dim a As Integer 'what is the workbook and sheet name we're in now? s = ActiveWorkbook.Name + "_" + ActiveSheet.Name t = Timer Do While Timer < t + 5 DoEvents If ActiveWorkbook.Name + "_" + ActiveSheet.Name < s Then a = 1 GoTo enditall End If Loop enditall: If a = 1 Then MsgBox "Quit because you changed sheets or workbooks." Else MsgBox "Finished the timer loop" End If End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
You are right about the End bit. If anything, I use Exit <KeyWord like in
this case, Exit Sub. However, even that has to be used in a proper structure. The angle I was thinking, your Deactivate Event would have the following statement: m_bolWorksheetActive = False Then within your For...Next block, put in the following code: DoEvents If m_bolWorksheetActive Then Application.StatsBar = lng Else 'If you want, you can put in some other command as to the reason for exiting the For block early 'after this line, and before the Exit For line. Exit For End If -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Jim Thomlinson" wrote in message ... I tried this and it sort of worked to halt code... On a sheet I added a command button with the following code... Dim lng As Long For lng = 1 To 10000000 DoEvents Application.StatusBar = lng Next lng Application.StatusBar = False Then in the Deactivate event of thisworkbook I added Private Sub Workbook_Deactivate() End End Sub if you switch sheets with either Ctrl+Tab Window | Select another sheet It executes the End and halts all code. Interestingly you can not just click on the sheet in the Task Bar... Of couse End is generally speaking a bad idea and I do not endorse the use of this code but I wanted to prove that I'm smart... Best I could manage was to sort of prove it so I guess I'm sorta smart. ;-) -- HTH... Jim Thomlinson "Dave Peterson" wrote: Take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
I'll buy into the fact you're smart--too smart to recommend using End <vvbg.
I think I'd stay with the way Chip shows. Jim Thomlinson wrote: I tried this and it sort of worked to halt code... On a sheet I added a command button with the following code... Dim lng As Long For lng = 1 To 10000000 DoEvents Application.StatusBar = lng Next lng Application.StatusBar = False Then in the Deactivate event of thisworkbook I added Private Sub Workbook_Deactivate() End End Sub if you switch sheets with either Ctrl+Tab Window | Select another sheet It executes the End and halts all code. Interestingly you can not just click on the sheet in the Task Bar... Of couse End is generally speaking a bad idea and I do not endorse the use of this code but I wanted to prove that I'm smart... Best I could manage was to sort of prove it so I guess I'm sorta smart. ;-) -- HTH... Jim Thomlinson "Dave Peterson" wrote: Take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
Wow! I leave the office for a couple hours and get tons to digest. Thanks to
all! I am a real beginner with VBA and didn't realize until one of you mentioned that there is a worksheet deactivate event. This is what ended up working (the whole code): Option Explicit Private bStopTimer As Boolean Sub StartTimer45min() bStopTimer = True Application.ScreenUpdating = True Dim tmr As Long bStopTimer = False tmr = Timer Range("k4,e4") = "" Do Range("k4").Value = Int(Timer - tmr) DoEvents If bStopTimer Then Exit Do Loop Until Timer tmr + 2700 If bStopTimer Then Range("k4").Value = 2700 Else Range("e4") = "Time's Up!" End If End Sub Sub quitTimer() bStopTimer = True End Sub Private Sub Worksheet_Deactivate() bStopTimer = True End Sub The deactivate code right above stops the timer when you change the worksheet!! I would never have known without all your replies (which are really fancy and for the most part over my head :-)) I am currently working on figuring out how to add a pause button to the code above. If I figure that out I could change my deactivate code to the pause code (instead of stop) and when a user comes back to the worksheet they can just continue. I'm having a tough time figuring that out so help would be appreciated in that attempt if you have time. Thanks! "Dave Peterson" wrote: I'll buy into the fact you're smart--too smart to recommend using End <vvbg. I think I'd stay with the way Chip shows. Jim Thomlinson wrote: I tried this and it sort of worked to halt code... On a sheet I added a command button with the following code... Dim lng As Long For lng = 1 To 10000000 DoEvents Application.StatusBar = lng Next lng Application.StatusBar = False Then in the Deactivate event of thisworkbook I added Private Sub Workbook_Deactivate() End End Sub if you switch sheets with either Ctrl+Tab Window | Select another sheet It executes the End and halts all code. Interestingly you can not just click on the sheet in the Task Bar... Of couse End is generally speaking a bad idea and I do not endorse the use of this code but I wanted to prove that I'm smart... Best I could manage was to sort of prove it so I guess I'm sorta smart. ;-) -- HTH... Jim Thomlinson "Dave Peterson" wrote: Take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop all macros in worksheet when left
There's a worksheet_activate event, too.
But be careful. If you swap to a different workbook, neither of these events will fire. You'll want to look at Workbook_WindowActivate and Workbook_WindowDeactivate (in ThisWorkbook). dgold82 wrote: Wow! I leave the office for a couple hours and get tons to digest. Thanks to all! I am a real beginner with VBA and didn't realize until one of you mentioned that there is a worksheet deactivate event. This is what ended up working (the whole code): Option Explicit Private bStopTimer As Boolean Sub StartTimer45min() bStopTimer = True Application.ScreenUpdating = True Dim tmr As Long bStopTimer = False tmr = Timer Range("k4,e4") = "" Do Range("k4").Value = Int(Timer - tmr) DoEvents If bStopTimer Then Exit Do Loop Until Timer tmr + 2700 If bStopTimer Then Range("k4").Value = 2700 Else Range("e4") = "Time's Up!" End If End Sub Sub quitTimer() bStopTimer = True End Sub Private Sub Worksheet_Deactivate() bStopTimer = True End Sub The deactivate code right above stops the timer when you change the worksheet!! I would never have known without all your replies (which are really fancy and for the most part over my head :-)) I am currently working on figuring out how to add a pause button to the code above. If I figure that out I could change my deactivate code to the pause code (instead of stop) and when a user comes back to the worksheet they can just continue. I'm having a tough time figuring that out so help would be appreciated in that attempt if you have time. Thanks! "Dave Peterson" wrote: I'll buy into the fact you're smart--too smart to recommend using End <vvbg. I think I'd stay with the way Chip shows. Jim Thomlinson wrote: I tried this and it sort of worked to halt code... On a sheet I added a command button with the following code... Dim lng As Long For lng = 1 To 10000000 DoEvents Application.StatusBar = lng Next lng Application.StatusBar = False Then in the Deactivate event of thisworkbook I added Private Sub Workbook_Deactivate() End End Sub if you switch sheets with either Ctrl+Tab Window | Select another sheet It executes the End and halts all code. Interestingly you can not just click on the sheet in the Task Bar... Of couse End is generally speaking a bad idea and I do not endorse the use of this code but I wanted to prove that I'm smart... Best I could manage was to sort of prove it so I guess I'm sorta smart. ;-) -- HTH... Jim Thomlinson "Dave Peterson" wrote: Take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx He includes notes on how to kill a pending (not running!) macro. I'm not sure how you'd stop a running macro via code. (I'm gonna guess it's impossible--now that should challenge some smart people <vbg.) dgold82 wrote: Is there code that I can put in a worksheet module that automatically stops all the running macros when a user leaves that worksheet? My problem is that I have a timer macro that runs and if a user leaves that worksheet before it completely counts down then the screen becomes all garbled until the macro is stopped. Thanks! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stop a clicked on cell being left blank | Excel Worksheet Functions | |||
How do I stop radio buttons moving left after Printing | Excel Discussion (Misc queries) | |||
How to change the right-to-left worksheet to left-to-right workshe | Excel Discussion (Misc queries) | |||
Moving Cell up, down, left or right thru Macros | Excel Programming | |||
Macros - stepping left in a macro | Excel Programming |