0

I have created a ComboBox to do a Google-style search of Cities. I have my ComboBox on sheet 1, that is linked to a dynamic range of cities on sheet 2. The ComboBox output cell is what changes the dynamic range.

Here’s the problem I’m having. I start typing “sea” and I get two results in my drop down list-seaport and Seattle. So far so good. Then I use the down arrow to Select Seattle and the program crashes and restarts. I believe it is because when I hit down one time, seaport is selected and this narrows down my range to just the one option and Seattle disappears.

Is there any way around this? Either disabling the use of the keyboard down arrows for selections or preventing the output to the linked cell until a selection is finalized?

This the code for my combobox on Sheet1

Private Sub TempCombo_Change()
   TempCombo.ListFillRange = "Cities"
   Me.TempCombo.DropDown
End Sub

The named range "Cities" is defined by formula on sheet 2

 ='City Data'!$L$2:INDEX('City Data'!$L$2:$L$570,MAX('City Data'!$K$2:$K565,1))
Tabitha Murillo
  • 11
  • 3
  • Share the code you're using and maybe some print screens. otherwise it's rather difficult to identify what is it making it to crash. – Ricardo Diaz Oct 22 '19 at 16:16
  • 1
    This the code for my combobox on Sheet1 `Private Sub TempCombo_Change() TempCombo.ListFillRange = "Cities" Me.TempCombo.DropDown End Sub` The named range "Cities" is defined by formula on sheet 2('City Data') `='City Data'!$L$2:INDEX('City Data'!$L$2:$L$570,MAX('City Data'!$K$2:$K565,1))` – Tabitha Murillo Oct 22 '19 at 16:21
  • @TabithaMurillo welcome to the site! Sometimes I've found the issue is doing too much in the change event, see if this [answer](https://stackoverflow.com/questions/42162379/excel-crash-when-trying-to-autocomplete-an-activex-combobox) is any help here. – Jimmy Smith Oct 22 '19 at 17:56
  • 1
    @JimmySmith Thank you for the response. I tried adding the `.ListFillRange` to a Workbook_Open event and it resolved the crashing issue, but now when I type in "sea" into my ComboBox I get the two results (Seaport and Seattle), followed by three blank result lines, and this five line pattern repeats down the scrollbar for about 200 more lines. I don't understand why moving the range would cause this issue. – Tabitha Murillo Oct 22 '19 at 18:42
  • Nice, we're halfway there. Before you type in the box, does the list have any duplicates at that point? – Jimmy Smith Oct 22 '19 at 18:57
  • No duplicates, no blanks, just the complete list. – Tabitha Murillo Oct 22 '19 at 19:31
  • That's bizarre - I don't think it's related to moving the code to that event, but something could be firing the _Open more than once. Do you have any other code that may be affecting how that is populated? – Jimmy Smith Oct 22 '19 at 20:05
  • I don't have any other VBA code entered. I also encountered this issue when I change the TempCombo event type from _Change() to anything else. My Named range is determined from formulas in 3 helper columns on sheet 2 that narrow down the results list as the user types. I'm using the process listed on – Tabitha Murillo Oct 22 '19 at 20:23

0 Answers0