Combo Box Dropdown

[Twollaston]

Good day all,

I am having an issue with my combo box. I have the combo box that is linked to a search box, and the search box output displays in the combo box so as I'm typing shows me all the entries that match may current input.

The issue is, when I tab out of the combo box and start inputting into the next cell, the combo box drop down menu opens up until I manually close it. I think it happens basically anytime the sheet is recalculated. I was wondering fi there is a way to only show the drop down when I'm typing, so that it doesn't pop open when i'm on other parts of the sheet.


The formulas in the range that populate my search output look as follows =IFERROR(INDEX('County ID Lookup'!$A$2:$A$3443,MATCH(ROWS('County ID Lookup'!$D$2:D2),'County ID Lookup'!$D$2:$D$3443,0)),"")

This is the code I have on the page:

Private Sub ComboBox1_Change()
ComboBox1.DropDown

End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then
        Range("M6").Activate
    End If
End Sub

(the bottom sub is so I can use the tab key to get out of the combo box)

If you have any suggestions to fix this I would really appreciate it.

 

[Akuini]

I have the combo box that is linked to a search box

1. What do you mean by "search box"? Is it a cell, i.e cell M6?
2. Can you explain the steps of what you do? Like, you type something in M6, then what? You manually get the cursor to the combobox? then what?
3. Could you upload your sample workbook to a free site such as dropbox.com & then put the link here?

 

[Twollaston]

1. What do you mean by "search box"? Is it a cell, i.e cell M6?
2. Can you explain the steps of what you do? Like, you type something in M6, then what? You manually get the cursor to the combobox? then what?
3. Could you upload your sample workbook to a free site such as dropbox.com & then put the link here?

It's exactly like this.

Essentially I have a dynamic list(Z4:Z3445) that appears in a range of cells based on what I type in the search box(which is a cell, X4, that is linked to the combo box, L6) so as I type in the combo box it appears in the cell that all the search formulas are linked to(X4). And the combo box outputs the range of the dynamic list in it's dropdown. This makes it so the combo box gives me auto-populated suggestions in the drop-down, based on what I'm typing. M6 itself has nothing to do with the search box, it's just the next field of information I would need to load into my database after finding the data validated county in the combo box.

I run these formulas side by side to my data.(data is A2:A3443)
Column C Formulas =--ISNUMBER(IFERROR(SEARCH('Export Dealer LMA table to HAI'!$X$4,A2:A3443,1),""))
Column D Formulas =IF(C2=1,COUNTIF($C$2:C2,1),"")

And then I have this formula in a range of cells to collect the output. (the range of the dynamic list is (Z4:Z3445)
=IFERROR(INDEX('County ID Lookup'!$A$2:$A$3443,MATCH(ROWS('County ID Lookup'!$D$2:D2),'County ID Lookup'!$D$2:$D$3443,0)),"")

Unfortunately, I am unable to share the workbook due to legal reasons and security on our network. I will be able to recreate with dummy data it this weekend when I get back to my home computer if needed.

I was just hoping there is some code maybe that I can put in the page source that will stop the combo box from opening when cells recaulculate upon entering data or double clicking. (I need the recalculate) I know I'm not giving a lot to go on. I was just hoping it's an easy fix I'm missing. If this isn't enough info, I will recreate the data and link the workbook.

 

[Akuini]

Essentially I have a dynamic list that appears in a range of cells based on what I type in the search box(which is a cell, X4, that is linked to the combo box, L6) so as I type in the combo box it appears in the cell that all the search formulas are linked to.

It isn't clear to me what you're doing. When you do the searching, do you type the keyword in the cell X4 or in the combobox?
I think it's in the combobox, right?

You can try this:
1. Clear the LinkedCell property of the combobox.
2. And try adding the blue line:

Private Sub ComboBox1_Change()
Range("X4") = ComboBox1.Value
ComboBox1.DropDown

End Sub