Excel ActiveX Combobox clears itself after entry selection

Question

I have an activeX combobox set up in my excel spreadsheet as per the following tutorial:

https://www.extendoffice.com/documents/excel/2439-excel-drop-down-list-search.html

I've made some changes to adjust for the use of some option buttons so different entry types can be used. The dropdown code looks like several if-repetitions of this:

If OptionButton1 = True Then 'determine option material is selected
    ComboBox1.LinkedCell = "Form!B3"
 'set cell linked to box to material type cell when option is selected
    ComboBox1.ListFillRange = "DropDownList1" 'set dropdown list as materials when option is selected
    Me.ComboBox1.DropDown 'dropdown list

Again adjusting for the use of option buttons.

This used to run flawlessly. However recently it has developed an issue that after selecting an option, the combobox decides to clear itself. I have rewritten my code several times, rewritten the formula's several times, and changed every single combobox property to determine what the problem is. The only issue I can think of if that after selection of a value, the list I use to fill the combobox changes as it searches the new entry. Afterwards the list and selected entry do not match up anymore and the combobox produces an error. Is there any way I can prevent this from happening?

One of the working workarounds is to click into the linked cell before it clears itself and pressing enter. This treats the entry as user-written and doesn't produce this error. One of the odd things is that I have two identical comboboxes set up like this, and only one produces this error. Another spreadsheet I use which is almost identical apart from the datasets has the same issue, but it is the second combobox that doesn't work instead of the first one. There is no difference in the code, as it's been copy-pasted.

Edit: I've been at this problem for months now before posting it here, and this morning I finally found a workaround solution. It slows the search and it's very ugly, but it works fairly reliable and stops the error from causing issues.

What I've done is put a combobox on-change event in which replaces the linked cell with it's own value. So if the combobox clears itself the linked cell will hold its value as required.

Private Sub ComboBox1_Change()
If OptionButton1 = True Then 'determine material option is selected
Sheet1.Range("B3") = Sheet1.Range("B3").Value

Note: This is obviously no solution to the problem, but a viable workaround. If someone knows how to prevent the problem altogether I would be grateful.

It's really hard to tell without more information. What is "DropDownList1"? Is it a named range? — HackSlash
It is yes, it is a named range containing the formulas used to find matches in the combobox. — Plutian
Nothing I'm afraid, which makes it harder for me to determine the issue. It works, and then a second later it clears itself. I've added a breakpoint at the point just before it clears itself, ran through the calculations step by step to see what triggers the error, but it works as expected then, and the error doesn't reproduce. — Plutian

HackSlash HackSlash · Accepted Answer · 2019-08-19T15:38:39

Looks like you are missing an equal sign on that one:

ComboBox1.ListFillRange = "=DropDownList1"

Excel ActiveX Combobox clears itself after entry selection

1 Answers