Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
turn off subroutines
I have some private subroutines that I would like to turn off so the
worksheet can be edited manually if incorrect data has been typed in during a race. Is it possible to turn off some subroutines and leave others working. The only subroutine I would need to temporarily disable is a private sub worksheet_change subroutine. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
turn off subroutines
Hi
Use an unused cell far away from your used range, in this example AA1 as control cell. If you want to turn off the event macro, enter TurnOff in the cell, and do your changes as needed, then remove the word to turn it on again. Private Sub Worksheet_Change(ByVal Target As Range) If Range("AA1") = "TurnOff" Then Exit Sub ' Here goes your original code End Sub Hopes this helps. .... Per On 27 Jul., 11:46, NDBC wrote: I have some private subroutines that I would like to turn off so the worksheet can be edited manually if incorrect data has been typed in during a race. Is it possible to turn off some subroutines and leave others working. The only subroutine I would need to temporarily disable is a private sub worksheet_change subroutine. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
turn off subroutines
I can make that work thanks, but it gave me another idea. The only time i
need the Private Sub Worksheet_Change(ByVal Target As Range) to work is when one of my main subs in the public module is going and a form is operating (the main sub loads and shows the form). Can I put something in these subs that disables the worksheet_change sub when the form is shut down. Thanks "Per Jessen" wrote: Hi Use an unused cell far away from your used range, in this example AA1 as control cell. If you want to turn off the event macro, enter TurnOff in the cell, and do your changes as needed, then remove the word to turn it on again. Private Sub Worksheet_Change(ByVal Target As Range) If Range("AA1") = "TurnOff" Then Exit Sub ' Here goes your original code End Sub Hopes this helps. .... Per On 27 Jul., 11:46, NDBC wrote: I have some private subroutines that I would like to turn off so the worksheet can be edited manually if incorrect data has been typed in during a race. Is it possible to turn off some subroutines and leave others working. The only subroutine I would need to temporarily disable is a private sub worksheet_change subroutine. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
turn off subroutines
A couple of ways to do that:
declare a public boolean 'flag' and set it to TRUE inside of your form's code (the Initialize event would be a good place to do that) and just before you UNLOAD it, set it back to FALSE. Test the status of that flag at the start of subs you don't want to run when the form is loaded and if it is TRUE, then just exit the sub, or Use a routine to test if the form is loaded and call that routine at the start of the subs you don't want to run and if the form is loaded, again, just exit the sub. Do a web search for "test if excel form is loaded" and you'll find several code samples for such a function. Will actually need to UNLOAD the form for them to work, not just .Hide it. "NDBC" wrote: I can make that work thanks, but it gave me another idea. The only time i need the Private Sub Worksheet_Change(ByVal Target As Range) to work is when one of my main subs in the public module is going and a form is operating (the main sub loads and shows the form). Can I put something in these subs that disables the worksheet_change sub when the form is shut down. Thanks "Per Jessen" wrote: Hi Use an unused cell far away from your used range, in this example AA1 as control cell. If you want to turn off the event macro, enter TurnOff in the cell, and do your changes as needed, then remove the word to turn it on again. Private Sub Worksheet_Change(ByVal Target As Range) If Range("AA1") = "TurnOff" Then Exit Sub ' Here goes your original code End Sub Hopes this helps. .... Per On 27 Jul., 11:46, NDBC wrote: I have some private subroutines that I would like to turn off so the worksheet can be edited manually if incorrect data has been typed in during a race. Is it possible to turn off some subroutines and leave others working. The only subroutine I would need to temporarily disable is a private sub worksheet_change subroutine. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - passing Variables to subroutines | Excel Discussion (Misc queries) | |||
Turn Off New UI | New Users to Excel | |||
WHY DOES 1E4 TURN INTO 1.00 E + 04? THANKS | Excel Discussion (Misc queries) | |||
How do i turn it off | Excel Discussion (Misc queries) | |||
how do i turn off the gridlines | Excel Discussion (Misc queries) |