VBA combobox dropdown list folded up event.

[keromero]

VBA combobox dropdown list folded up event.

I want to fire a message box as soon as drop down is folded up After selection made.

Change event is not helping it keeps combobox list open during message show up.

After event neither helps it needs another trigger action.

So How could I do?

Thanks in advance

 

[Jaafar Tribak]

Introduce a delay before showing the MsgBox. You can use the OnTime function to call the MsgBox but, the MsgBox must be in a SUB in a separate Standard Module.

Alternatively, if you want to keep the entirety of the code inside the userform module (assuming the combobox is in a userform) , use this trick:

In the UserForm Module:

Option Explicit

Public DisableEvents As Boolean

Private Sub ComboBox1_Change()
    FoldUpComboBox ComboBox1
    MsgBox ComboBox1.Value
End Sub

Private Sub FoldUpComboBox(ByVal Combo As ComboBox)
    DisableEvents = True
    Controls.Add("Forms.TextBox.1", "DynamicTextBox", True).SetFocus
    Controls.Remove "DynamicTextBox"
    Combo.SetFocus
    DisableEvents = False
End Sub

Private Sub ComboBox1_Enter()
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

Private Sub UserForm_RemoveControl(ByVal Control As MSForms.Control)
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

 

[Jaafar Tribak]

Sorry, replace the above code with the following :

Option Explicit

Public DisableEvents As Boolean
Public DropButtonClicked As Boolean

Private Sub ComboBox1_Change()
    FoldUpComboBox ComboBox1
    MsgBox ComboBox1.Value
End Sub

Private Sub FoldUpComboBox(ByVal Combo As ComboBox)
    If DropButtonClicked Then
        DisableEvents = True
        Controls.Add("Forms.TextBox.1", "DynamicTextBox", True).SetFocus
        Controls.Remove "DynamicTextBox"
        Combo.SetFocus
        DisableEvents = False
    End If
    DropButtonClicked = False
End Sub

Private Sub ComboBox1_DropButtonClick()
    DropButtonClicked = True
    ' Rest of code here if any ...
End Sub

Private Sub ComboBox1_Enter()
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

Private Sub UserForm_RemoveControl(ByVal Control As MSForms.Control)
    If DisableEvents Then Exit Sub
    ' Rest of code here if any ...
End Sub

 

[keromero]

Dear Jaafar, above code did the trick. I just had to add Me.ComboBox.Enabled = True as initially the combobox was not enabled and I got error message.

Private Sub FoldUpComboBox(ByVal Combo As ComboBox)
DisableEvents = True
Controls.Add("Forms.TextBox.1", "DynamicTextBox", True).SetFocus
Controls.Remove "DynamicTextBox"
Me.ComboBox9.Enabled = True
Combo.SetFocus
DisableEvents = False
End Sub


Thanks for the help and Best Regards

 

[Jaafar Tribak]

Dear Jaafar, above code did the trick. I just had to add Me.ComboBox.Enabled = True as initially the combobox was not enabled and I got error message.

Glad it worked for you.

Having said that, please, don't use the first code, use the second one (post#3) as it is more accurate.

 

[keromero]

Hi Jaafar, for some reason second code "back me to the beginning". In case I use second code, Message box appears while the combobox drop down folded.

One minor issue, on the first code, while code is in operation there is a "flickering effect" on the userform. Such as an event taking action in the background. Is there a way to eliminate it?

Thanks

 

[Jaafar Tribak]

One minor issue, on the first code, while code is in operation there is a "flickering effect" on the userform. Such as an event taking action in the background. Is there a way to eliminate it?

That's probably because it brievly takes the focus to a dynamically created textbox (BTW, I didn't experience myself any flickering effect in my system)

Ok. Let's try something else that won't cause any screen flicker like sending the enter key and see how it works for you. This is less code and easier but not sure how reliable.

In the UserForm Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
#Else
    Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
#End If

Private Sub ComboBox1_Change()
    SendKeyAPI VBA.vbKeyReturn
    MsgBox ComboBox1.Value
End Sub

Private Sub SendKeyAPI(ByVal VKey As Byte)
    Const KEYEVENTF_KEYDOWN = &H0, KEYEVENTF_KEYUP = &H2
    Call keybd_event(VKey, 0, KEYEVENTF_KEYDOWN, 0)
    Call keybd_event(VKey, 0, KEYEVENTF_KEYUP, 0)
    DoEvents
End Sub

 

[keromero]

Hi Jaafar, thanks for your time and great efforts. This last code worked very well and flicker free at all as well.

However it looks it interfered a few other code which uses "If Response = vbOK" statement where I need to set focus to existing text box after msgbox okay click.
for some reason.

MsgBox "Enter a numeric value or a value greater than zero...", vbInformation,
If Response = vbOK Then TextBox1 = SetFocus
Cancel = True
TextBox1.Text = ""
Me.ComboBox6.Enabled = False

End If

 

[keromero]

Hi Jaafar, just a quick update...

I have managed final issue by moving Msgbox from exit event to before update event and deleted: "If Response = vbOK Then TextBox1 = SetFocus"

It works as follows:

MsgBox "Enter a numeric value or a value greater than zero...", vbInformation,
Cancel = True
TextBox1.Text = ""
Me.ComboBox6.Enabled = False

 

[Jaafar Tribak]

OK- Glad you got this sorted out.