How do I get the ListIndex of an Item in a Combobox when I have the Value, so I can remove the Item-CodePudding

I have a Listbox SkuList whose list is equal to the list of a seperate Combobox SkuBox. What I am trying to do, is remove the value chosen for the Combobox SkuBox from the Listbox SkuList.

Private Sub UserForm_Activate()

Dim Esh As Worksheet
Set Esh = ThisWorkbook.Sheets("Result")
Dim r_sku As Range

If Esh.Range("A2") <> "" Then
For Each r_sku In Esh.Range("A2", Esh.Range("A2").End(xlDown))
    Me.SkuBox.AddItem r_sku.Value
Next r_sku
End If

Me.SkuList.List = Me.SkuBox.List
End Sub

Private Sub SkuBox_Change()
Me.SkuList.List = Me.SkuBox.List
Me.SkuList.RemoveItem (SkuBox.Value)
End Sub

This last section about removing the item doesn't work, because .RemoveItem requires a ListIndex. My question is, how do I get the ListIndex of the SkuBox.Value?

CodePudding user response：

You can use ListIndex, but that will not be accurate across controls. As soon as you start removing items, the indexes will be different for items in SkuList vs SkuBox.

I'm not up on my VBA, but I don't see any ID properties, so you'll need to do as stated. Loop thru and find the item by text.

The following should work, but my advice would be to get in there and play with this. If you debug and step through the code, the intellisense will give you a lot of information that you can use to discover properties and figure out how to do things.

Private Sub SkuBox_Change()

  Dim selectedSkuBoxValue As String
  selectedSkuBoxValue = SkuBox.Value

  SkuList.ListIndex = -1

  Dim listItem
  For Each listItem In SkuList.List
      If listItem = selectedSkuBoxValue Then
          SkuList.Value = selectedSkuBoxValue
          Exit For
      End If
  Next listItem

  If SkuList.ListIndex > -1 Then
      SkuList.RemoveItem SkuList.ListIndex
  End If

End Sub