Combo control to execute code BEFORE change - please help

[davidnicel]

Hello,

First timer here . This issue has been drving me up the wall and
after traweling the Internet I am no wiser.

I want to execute some code when the user clicks on a combo box,
before they make a selection. E.g. "Do you want to save changes? Yes/
No." Yes runs code (a), No runs code (b).

I have tried using the event "DropButtonClick". It works when the
user first clicks on the drop button. However, when the user makes a
selection, it executes for a second time. Most annoying!

Private Sub cmbo_project_dropbuttonclick()
Dim Res As Variant

Res = MsgBox("Do you want to save changes?", vbYesNo, "Save
Changes?")

If Res = vbYes Then
'code (a)
Else
'code (b)
End If
End Sub


I have tried using the event "MouseDown" and entering the X & Y
coordinate of the drop down arrow, so that the code is only run if the
arrow is clicked. This worked, however the combo box ignored whatever
selection was made. Most annoying!

Private Sub cmbo_project_MouseDown(ByVal Button As Integer, ByVal
Shift As Integer, ByVal X As Single, ByVal Y As Single)

If 110.25 <= X And X <= Me.cmbo_project.Width Then
Dim Res As Variant
Res = MsgBox("Do you want to save changes?", vbYesNo, "Save
Changes?")

If Res = vbYes Then
'copyValueScanData
Else
'resetValueScanFormulas
End If
End If

End Sub


Can you please help me with either of these approaches or suggest
another one. I thought it was be relatively simple to execute code as
soon as a combo box is selected, am I missing something?

You're help is much appreciated,

Kind regards,

David

 

[michael.beckinsale]

Hi David,

If you use the ComboBox_Change event the macro will only be triggered
whenever the selection is changed.

HTH

Michael

 

[davidnicel]

Hi David,

If you use the ComboBox_Change event the macro will only be triggered
whenever the selection is changed.

HTH

Michael

Yes. I don't want to do that. I want to trigger the macro BEFORE the
selection is changed. I.e. As soon as the user clicks on the drop
down box.

 

[JP]

Combo boxes have an Enter event which fires when you first enter it.
Does that work?

Private Sub ComboBox1_Enter()
MsgBox "Entered combo box"
End Sub


--JP

 

[davidnicel]

Thanks JP.

I don't have that event for some reason. Is it because my combo is on
a worksheet and not on a userform?

Kind regards,

David

 

[davidnicel]

Thanks JP.

I don't have that event for some reason.  Is it because my combo is on
a worksheet and not on a userform?

Kind regards,

David

I tested it and yes the Enter event is only on userforms.
Unfortunately, I need the combo on the worksheet itself.

Kind regards,

David

 

[davidnicel]

Anyone else got any suggestions?

Your help is much appreciated.

David

 

[Patrick Molloy]

well you can't do anything about the forms control when its on a spreadsheet
- though after the event can obviously be done

If you us ethe ActiveX control, then the GotFocus event is fired when the
combo is selected, and you can encode this as you want