ActiveX ComboBox deactivate

[rjplante]

I have an AxtiveX ComboBox that uses a list with a defined name of "Company" to fill the drop down menu. I also have used some fancy programming described in the link below to create a searchable list like when typing in a Google search window.

https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/

After I enter in my text and select the company I want, I have a small magnifying glass icon that I use to run a macro to search my database. I then use VBA to clear the contents of the linked cell to the ComboBox. It all works good except after my search is done, the ComboBox has a partial image of the drop down menu just below it and the name of the first company in my list appears there. The only way I can get it to disappear is to click inside the ComboBox like I want to type data in for another search. Once I click inside, the partial drop down image clears and I am back in business.

How can I use VBA to enter the ComboBox search window, so that the partial drop down menu image clears automatically. I have tried the following:

ComboBox1.value = Null
ComboBox1.SelText = ""
ComboBox1.ListIndex = -1
ComboBox1.Clear
ComboBox1.ListFillRange = -1
ComboBox1.select
ComboBox1.activate

All of these did not mimic the action of entering the search box window on the screen and clearing the drop down menu image. I don't know what to try next or if I just have to live with this.

Thanks for any suggestions.

Robert

 

[Yongle]

Try this method and see if it clears the partial image
You may prefer to use a different trigger

Amend ComboBoxName if necessary

Select a new value in combobox and then click on any cell to trigger the macro

Paste into sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    [COLOR=#ff0000]ComboBox1[/COLOR].Activate
    SendKeys "{F4}", True
    SendKeys "{ESC}"
    Target.Select
End Sub

 

[rjplante]

My only problem with that is the entire sheet is locked down except for buttons to add a new record, remove expired records, or navigate to other screens. So there is no other cell for the user to click into.

 

[Yongle]

Use a different trigger
- a button?
- combobox LostFocus event?

 

[Jaafar Tribak]

My only problem with that is the entire sheet is locked down except for buttons to add a new record, remove expired records, or navigate to other screens. So there is no other cell for the user to click into.

Maybe something like this :

Private Sub ComboBox1_Change()
    If IsError(Application.Match(CStr(ComboBox1.Text), ComboBox1.List, 0)) = 0 Then
        Application.ScreenUpdating = False
        ComboBox1.Visible = False
        Application.OnTime Now, Me.CodeName & ".ShowCombo"
    End If
End Sub

Private Sub ShowCombo()
    ComboBox1.Visible = True
    DoEvents
    ComboBox1.Activate
End Sub