I've tried everything.
I have a combobox that is used to search a list of items and a macro that reads the value of the combobox and changes some things in the spreadsheet.
The problem is, after the macro has finished and has cleared everything ready for the next input, the dropdown for the combobox opens again and will only close if you select the box again. It also stays in the same place on the screen if you move the window around or change sheets in the workbook.
I've tried setting the value to null, setting the list width to itself, auto selecting a different cell, telling it to only open the dropdown if there is text in the box, changing the properties of the combobox, and a bunch of other stuff that didn't work. All the links are purple.
The only two things that have "worked" are:
Selecting the combobox at the end of my macro, which does get rid of the drop down but also breaks excel, so whenever you click on anything (cells, combobox) it copies? it but a few pixels down
and i have to re start excel for it to work again.
The other thing that works is removing .dropdown from the comboboxes code but this means that it wont show the search results unless you click on the dropdown arrow and that's not what I want.
Any suggestions would be very much appreciated.
Here's the main bit of code for the combobox search, the two quoted out .dropdowns seem to be causing the problem.
Private Sub ComboBox2_Change()
Dim i As Long
Dim searcher As String
Dim searchWords As Variant
Dim element As Variant
Dim regexOne As RegExp
searcher = ComboBox2.Value
searchWords = Split(searcher, " ")
Set regexOne = New RegExp
regexOne.Pattern = ""
If Not IsArrow Then
With Me.ComboBox2
.List = Worksheets("Stock List").Range("A2", Worksheets("Stock List").Cells(Rows.Count, "A").End(xlUp)).Value
.ListRows = Application.WorksheetFunction.Min(6, .ListCount)
'.DropDown
End With
With regexOne
.IgnoreCase = True
.Global = True
For m = LBound(searchWords) To UBound(searchWords) Step 1
For i = Me.ComboBox2.ListCount - 1 To 0 Step -1
.Pattern = searchWords(m)
If .Test(Me.ComboBox2.List(i)) = False Then Me.ComboBox2.RemoveItem i
Next
Next
'Me.ComboBox2.DropDown
End With
End If
End Sub
If it helps, I use:
ThisWorkbook.Sheets("Main").ComboBox2.ListIndex = -1
In my button click macro to clear the combobox, this also took me an age to find.
Bookmarks