Your explanation was fine. I just jumped to confusions. :-)
> However, what I theorized might still be reasonable. By linking
the combobox and the cell at object level, you're creating a pretty
strong bond. My feeling is that this bond would transcend anything
you could do in VBA. I also theorize that the bond itself would make
use of events to keep itself up-to-date. So it may just not be
listening to you.
Oooo. This sounds sort of trancedental (:-)
Seriously...
The ComboBox update (from Linked Cell) clearly trancends the Event
disabling. That is clear. I didn't see that this is considered to
be an Event, but I didn't read everything on VBA (:-). The
ComobBox_Change() is clearly advertized as a "standard" event and
(famous last words) should obey event commands. sigh...
> OTOH. You're changing the cell, which will cause the combobox to
change. But when does it change? It might be that it holds off on
that change until you finish what you're doing.
It appears to be immediate. I (all too) liberally sprinkle my code
with Debug.Prints and right after the Debug.Print before the line
which changes the cell [[ sheet1.Range(bla,bla).Value = NewValue ]]
I see the Debug.Print which is the first line in the
ComboBox_Change routine.
>> (Or it even might simply wait for you to enable the interrupts
again.)
No. I played to detect that... With code like this:
================================================
-------- Sheet event code area ------------
Sub ComboBox_Change()
Debug.Print" Change seen in Box-1 "
bla, bla
Debug.Print " Exit Change "
End Sub
-------------------------------------------
Somewhere in Kansas:
-------------------
Sub Kansas()
Debug.Print "Entering Kansas..."
...
Debug.Print" Changing linked cell _NOW_"
Application.EnableEvents = False
sheet1.Range("Linked_Cell").value = NewValue
Application.EnableEvents = True
Debug.Print " Exiting Kansas Sub"
End Sub
----------------------------------
The immediate window shows:
Entering Kansas...
Changing linked cell _NOW_
Change seen in Box-1
Exit Change
Exiting Kansas Sub
========================================================
''NOTE The Enable = True statement can also be in sub which called
the Kansas Sub (Obviously after returing there)...and I still get the
ComboBox firing before the Kansas exit message. I played with the
locations of the Events on/off lines trying to determine if there was
some "Intter-Sub Event locking" going on. Sort of a "scope of events
rule" like:
"Disabling events in a Sub still allows Events in Subs this one
calls."
> In both of those scenarios, the delayed response will mean that
interrupts will be active again before it happens.
Ahhhh! the 'ole hidden delay in emplementing your instruction ploy.
Well... I don't see any reference in VBA to Events suspension vs.
disabling.
Suspension = events don't fire, but they *ARE* captured for firing as
soon as they are enabled again.
Disabling being (assumed by me and appears to be true) that they are
not captured (lost, gone) at all during the disable time.
> But also that setting/unsetting any sort of "ignore" flag would
possibly not work either.
The thought occured, but again, it's sort of a brute force
solution - not out of the question. Set a flag before changing the
LinkedCell and clear it after...Then abort the code in the Change
event sub if it is set.