Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Rainhard Fischer   on Jan 17 In MS Office Category.

  
Question Answered By: Kanchan Ap   on Jan 17

I haven't read the whole thread, but regarding ignoring events; I
switched to a technique I learned here. I'm on Windows NT and it
seems to have very irregular event control using EnableEvents and
DoEvents. Since changing to this technique I have good control of
Change events.

Here's how.

Use a variable to abort the change  event handler when needed.

First.
Create a global variable called "Disabled".
It defaults to FALSE, so Change Events will execute right off.
[ You can even have several of these if you determine
you need to selectively disable different events. e.g.
ChangeDisabled, SelectionChangeDisabled, etc ]

Second.
When you want to ignore a Change Event, set  Disabled = True
Then change the cell value.

Sorta' like this:
Disabled = True ' Ignore Change
Cell(1,1).Value = 5
Disabled = False ' Back to normal


Third.
Early in your Event handler for value changes do like this:

Private Sub Worksheet_Change(ByVal target  As Excel.Range)
If Disabled Then Exit Sub
...
Bla Your Change handler code
...
End Sub


Make sure you exit gracefully. That is, take care of anything that
may have been set up for the Change sub and that needs to be un-done.
You can simply Goto a label right before the normal Sub cleanup (at
the end of the Sub).


If you need to do clean up differently than the normal exit does (I
did), you set up dual exits.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Disabled Then Goto MyExit
...
Bla, bla, bla Your Change handler code
...
Exit Sub ' The "normal" exit

MyExit: ' The abort exit
Cleanup
End Sub

Share: 

 

This Question has 17 more answer(s). View Complete Question Thread

 


Tagged: