Can't select ComboBox item (VBA)

simurq

Board Regular
Joined
Nov 11, 2011
Messages
73
That says it all! The combobox itself works OK but when I try to select ANY item from the drop-down list, I cannot. I know that the "Drop-Button-Click" event triggers the "Clear" method twice (upon opening and closing the list) but WHY it clears the selected item and how it can be solved remains vague to me. :oops:

Can you please help with this?

Code:
Private Sub deptRwy_DropButt*******()
    Dim FoundCell As Range, LastCell As Range
    Dim i As Long, j As Long, maxNo As Long
    Dim SearchCell As String, FirstAddr As String
    Dim aRwy() As Variant
        
    SearchCell = Range("D7").Value
    
    'Count total found cells in the range
    maxNo = WorksheetFunction.CountIf(Worksheets("Rwy").Range("A:A"), SearchCell)
    
    On Error Resume Next
    deptRwy.Clear
    
    With Worksheets("Rwy").Range("A:A")
        Set LastCell = .Cells(.Cells.Count)
        Set FoundCell = .Find(What:=SearchCell, After:=LastCell)
        ReDim Preserve aRwy(maxNo, 6)
    End With
            
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    
    For i = 0 To UBound(aRwy)
        If FoundCell Is Nothing Then
            Exit For
        Else
            For j = 1 To 7
                aRwy(i, j - 1) = FoundCell.Offset(, j)
            Next j
            Set FoundCell = Worksheets("Rwy").Columns(1).FindNext(After:=FoundCell)
            If FoundCell.Address = FirstAddr Then
                Exit For
            End If
        End If
    Next i

    For i = 0 To UBound(aRwy) - 1
        'Populate combobox with runway ID and magnetic heading in brackets
        deptRwy.AddItem aRwy(i, 0) & " (" & Round(aRwy(i, 4), 0) & "°)"
    Next i
End Sub

Thanks a lot in advance!!!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,215,006
Messages
6,122,665
Members
449,091
Latest member
peppernaut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top