Thanks for the tips.

I did get roughly the behavior that I needed by handling "Click" to set
focus on a button next to the combo box - this effectively got rid of focus
when the user picked something from the list and forced the events to fire.

Unfortunately I ran into an obscure bug having to do with multiple combo box
click handlers on the same form and finally gave up in disgust. I'm now
keeping track of the last selection that the user made, and modified my
Change handler to switch to the old entry in the cases where I wanted to
prevent changes. Should work well enough.


"sebastienm" wrote:

Now concerning the BeforeUpdate, it is happening right before the focus is
leaving the control, and by setting Cancel to True, you prevent the focus to
change to another control and force the focus to stay in the combo.
Now if you are trying to validate some values, you might want to save the
value of the combo at the _Enter event into a variable, say old_combo,
validate during the _BeforeUpdate to reset to old_combo if the validation
fails and set Cancel to True.

I hope this helps, ... but i have the feeling it is not what you are looking
for.

Seb

"Rick Lowe" wrote:

Thanks for the reply, I think I might not have been clear enough in my
question.

I used to be using the "Change" event, which was triggered every time the
selection was changed which is what I wanted. I now need to add some
validataion, so that I can cancel the change if the user has unsaved changes
on the form.

It seems that "BeforeUpdate" does not fire until the control loses focus (or
the user hits enter), probably because the combo box is trying to accept
input from the keyboard. I'm looking for a way to get BeforeUpdate to fire as
soon as something is selected from the drop down list. I did set the
"MatchRequired" property to True, which didn't seem to help.


"sebastienm" wrote:

Hi Rick,
You can deselect an item by setting the listindex property of the combo to
-1 right after it has been selected.You just have to make sure you only
proces your macro code when an item is selected (else do nothing):

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = 0 Then
'case there is a selection
'at the end of the code, set the selection to <none by setting
listindex to -1
MsgBox ComboBox1.Value
ComboBox1.ListIndex = -1
Else
'do nothing
End If
End Sub

"Rick Lowe" wrote:

I using Excel 2003 SP1. I'm currently working on a user form where I need to
be able to cancel a selection from a ComboBox in some circumstances. I am
only using the ComboBox because I want a drop-down list, I do not care about
adding new entries.

As soon as I replaced the "Change" event handler with paired BeforeUpdate
and AfterUpdate events, I noticed that neither event is firing until the
ComboBox loses focus - in other words I have to select an item from the list
and then tab away before anything happens.

When I was using the Change event (and didn't try to do validation), the
event fired as soon as an entry was selected from the list.

Is there any way around this behavior change (e.g. if BeforeUpdate is
defined do I need to do something with the Click event too?)

Thank you in advance for any ideas.

- Rick Lowe