Quick stupid question about "Sub ComboBox1_Click()"

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
Since I'm not that well versed in VBA, unlike most on this forum, I was hoping someone could answer a quick question regarding "Sub ComboBox1_Click()".

I thought this was a mouse click event, but as I seem to be finding out, it is also a key click event.

Is this correct?

I'm wondering, because I was trying to only use a mouse click to activate the drop-down in my ComboBox, but even the up/down keys are running the Sub ComboBox1_Click().

Thanks for any replies.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Yes, the ActiveX control works that way. As you navigate with keys, it is selecting and changing a value. This is what happens when you click a different value in the list.

There are coding ways to avoid that issue.
 
Last edited:

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
Thank you for the info, Ken.

Do you happen to know what code is used to avoid this issue?
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Code:
'https://answers.microsoft.com/en-us/msoffice/forum/all/disable-userform-combobox-change-event-when-arrow/598b44a1-dcda-4a2c-8e12-2b84762f98ae
Dim ChangeNotOk As Boolean

Private Sub ComboBox1_Click()
If ChangeNotOk Then
    ChangeNotOk = False
   'User pressed arrow up or arrow down, do nothing
Else
    'your current change event code
    MsgBox "Click Event Fired Here"
End If
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 38 Or KeyCode = 40 Then
        ChangeNotOk = True
    End If
End Sub

Private Sub ComboBox1_Change()
    MsgBox "Change Event Fired"
End Sub
 

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
Awesome Ken! Thanks, I'll try it in a moment.
 

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
I modified it as needed to get everything to work, and I GREATLY appreciate the help!

Sorry, one final thing...

In your code, I added "ElseIf KeyCode = 13 Then ChangeNotOk = False" in the "ComboBox1_KeyDown" sub, but I don't know how to get it to jump to and run the code in the "ComboBox1_Click" sub if the RETURN key is pressed.
 
Last edited:

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
Disregard. It was an easy fix. I just had to use Call ComboBox1_Click after the ElseIf.

All set. Thanks again Ken!
 

Forum statistics

Threads
1,082,586
Messages
5,366,476
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top