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