CODEWITHSUNDEEP
vbaexcelcomboboxactivex
Da Spotz
Da Spotz

Reputation: 200

Clearing text from otherwise empty combobox (active x)

I have a bunch of Comboboxes (ActiveX elements) and want to loop through all of them to fill them if there's an x next to it and empty them if there is not. This already works, but I'm getting an error when I try clearing the text/value of the the combobox after it has been emptied. Any ideas why?

Dim ws2 as worksheet
Dim ComBx As OLEObject
Dim Name As String
Dim NameParaWS3
Dim ComboFill As Range
Dim VisibleFill As Range

For Each ComBx In ws2.OLEObjects
ComBx.ListFillRange = ""
    If ComBx.progID Like "Forms.ComboBox.1" Then
        If ws2.Cells(ComBx.TopLeftCell.row, AlphaCol).Value = "X" Then
        Name = ws2.Cells(ComBx.TopLeftCell.row, 2).Value
        Set NameParaWS3 = ws3.Range("1:1").Find(Name, LookAt:=xlWhole)
        Set ComboFill = ws3.Range(ws3.Cells(2, NameParaWS3.Column), ws3.Cells(LastRow3, NameParaWS3.Column))
        Set VisibleFill = ComboFill.SpecialCells(xlCellTypeVisible)
            Debug.Print ComBx.Name & " located at " & ComBx.TopLeftCell.Address(False, False, xlA1) & ", belongs to parameter '" & Name & "' and is alphanumeric"
            With ComBx
                .ListFillRange = ComboFill.Address(0, 0, xlA1, True)
            End With
        Else:   ComBx.ListFillRange = ""

 'This is the part where I'm getting the error.None of these worked:
                'ComBx.Clear
                'ComBx.Value = ""
                'ComBx.Text= ""
        End If
    End If
Next ComBx

Upvotes: 0

Views: 165

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Use the .Object property of the OLEObject to retrieve the ComboBox object and its usual methods.

ComBx.Object.value = ""

You can even dim a variable a Combobox and have Intellisense for its methods:

Dim cmb as ComboBox: Set cmb = ComBx.Object
cmb.value = ""

Upvotes: 2

Related Questions