CODEWITHSUNDEEP
excelvba
Zainedevontay
Zainedevontay

Reputation: 17

Removing Certain Item from List

I have this piece of code that will look for a certain Sheet's column and populate the combo box with a distinct value. My problem here is that I need to remove one of the specific value. Kindly needed your help!

For example:

a
b
c
a
b

Based on my code below, the combo box would populate a,b,c

Dim wslk As Worksheet
 Set ws = Worksheets("W1")
 With ws
  t1 = .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).row

On Error Resume Next
 For y = 2 To t1
 Set c = .Cells(y, 2)
 Set t1rng = .Range(.Cells(2, 2), .Cells(y, 2))
  x = Application.WorksheetFunction.CountIf(t1rng, c)
   If x = 1 Then Cmb2.AddItem c
 Next y
 On Error GoTo 0
End With

I would like to know if there is any way for me to let's say remove "b" from the list. Thanks in advance!

Upvotes: 0

Views: 185

Answers (1)

FunThomas
FunThomas

Reputation: 29466

If you don't want a specific value in the list, simply exclude it while filling the ComboBox - eg If x = 1 AND c <> "B" Then

If you want to remove an entry from the ComboBox later, you can use the method RemoveItem. The following routine loops over all entries the the box, searches for a string and removes it. The entries are accessible via property List, index starts at 0.

Sub removeFromCombo(c As String)
    Dim i As Integer
    With Me.Cmb2
        For i = .ListCount - 1 To 0 Step -1
            If .List(i) = c Then .RemoveItem i
        Next i
        If .Value = c Then .Value = ""
    End With
End Sub

Upvotes: 2

Related Questions