Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
Hi David,
If you use the ComboBox_Change event the macro will only be triggered whenever the selection is changed. HTH Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
On 12 Mar, 11:06, "michael.beckinsale"
wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
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 On Mar 12, 7:08*am, wrote: On 12 Mar, 11:06, "michael.beckinsale" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
On 12 Mar, 11:20, wrote:
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
Anyone else got any suggestions?
Your help is much appreciated. David |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo control to execute code BEFORE change - please help
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 " wrote: Anyone else got any suggestions? Your help is much appreciated. David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change font in a forms control combo box | Excel Discussion (Misc queries) | |||
what is code to activate a combo box in control toolbox | Excel Discussion (Misc queries) | |||
combo box on change code | Excel Discussion (Misc queries) | |||
execute code in one worksheet when change occurs in another | Excel Programming | |||
No change event on control toolbox combo box when selection is the same? | Excel Programming |