turn off subroutines

[NDBC]

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

 

[Per Jessen]

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

 

[NDBC]

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

 

[JLatham]

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.