Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Event Woes

  Asked By: Jawna    Date: Oct 30    Category: MS Office    Views: 689
  

Poked around MSDN & Support.uSoft to no avail. A while back I couldn't
get WinNT to do DoEvents correctly (its a bit protective of 'em) and
now I can't get then to shut off!

Q:
Do Events have something like variable scope / lifetime?

Problem:
Setup pretty simple.
I have a Combo box in a sheet, linked to a cell and it works fine and
fires an event and executes the ComboBox_Change() Sub when the user
(manually) change the combo box value.

There are times, however, when I want to change the value of the linked
cell (via the execution of code), but NOT fire the Change Sub for the
ComboBox, sheet, or ThisWorkbook. (I do want the value displayed in
the comboBox to change appropriately). I don't want the ComboBox
change sub to run when I change the cell value with code. However,
the 'ole Excel Events think differently. While it turns out that
execution of that code, in this situation, isn't a disaster, it does an
operation that is redundant.

I use Application.EnableEvents = False in other areas, but I can't get
it to work for all situations and this is one.

Is there something about changing the value of a cell linked to a
control in a sub in a regular module , but not being able to disable
events connected with the control in the regular module?

[[by regular module I mean the ones at the bottom of the project
explorer -- not sheet, not forms, not class]]

It could be another one of NT's persnicketty characteristics...gotta re-
do this OS...

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Flynn Jones     Answered On: Oct 30

If a combobox is linked to a cell, then changing the combobox is going to
have to change the cell, no exceptions. This might well override an enable
events = false.

The question then is, why have a linked cell and a change event? Should you
be looking at a cell change event  instead?

Failing that, looks as though you'll just need to put an "ignore" variable
in for the combobox event to look at.

 
Answer #2    Answered By: Kanchan Ap     Answered On: Oct 30

you seem to have it backeards (Or I blew the explanation).
I want to change the *linked cell* via code. NOT the combo box
control... I have code to simply change the value in a cell (that
happens to be linked to a combo box).

A more fuller, better explanation:
I use the combo box to allow the user to choose values which are used
to perform an operation (sends it to an external device) AND save it
in the sheet (by virtue of the comboBox function to the linked cell)
for use next time the sheet is opened, like preferences.

When the sheet is closed, the value in that cell & combo box may
become stale because the value it represents is in an external device
that I am controlling via serial I/O in the macros. When I re-load
the sheet, I give the user the option of updating the external device
with the value in the sheet (re-send to the device)...or...or I want
to have the abiity to update the *sheet* to the value in the external
device. SO, I just want to change the linked cell and have the
comboBox show the updated value --- WITHOUT having the
ComboBox_Change event  trigger (as a result) thus re-sending the value
TO the device (as a result of my changing the linked cell...because I
just got it from there!
Like I say, not a disaster, just a time killer. But Disabling the
Events aughta woik!.

 
Answer #3    Answered By: Haya Yoshida     Answered On: Oct 30

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.

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. (Or it even might simply wait for you to
enable the interrupts again.) In both of those scenarios, the delayed
response will mean that interrupts will be active again before it happens.
But also that setting/unsetting any sort of "ignore" flag would possibly not
work either.

 
Answer #4    Answered By: Geneva Morris     Answered On: Oct 30

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.

 
Didn't find what you were looking for? Find more on Event Woes Or get search suggestion and latest updates.




Tagged: