Remove Item from Excel Listbox

Question

I need to remove items from a Listbox using data from Range under certain conditions. I don't want to delete data in the Worksheet the Listbox is based on.

Here is an excerpt of my code:

 Me.LstWSource.RowSource = ""

 If Len(rRange.Offset(1, 0).Formula) > 0 Then
    Set rRange = ThisWorkbook.Sheets(G_sNameReferenceS).Range(rRange.Offset(1, 0), rRange.End(xlDown))

 With Me.LstWSource
  .ColumnWidths = "28pt"
  .RowSource = rRange.Address
  .ListIndex = -1
  End With '>>>
End If

  '>>>>>>>>>>>>>>>>>>>>>>>>
  sRet = fG_SortingBasicS("lstYear", "DESC")
  '>>>>>>>>>>>>>>>>>>>>>>>>

  For i = Me.LstWSource.ListCount - 1 To 0 Step -1

   '>> current string condition to delete Item <<
   If Me.LstWSource.List(i) > CStr(Year(G_datJourTraitee)) Then
     Me.LstWSource.RemoveItem (i)
   End If
 Next i

The error is triggered by the RemoveItem.

score 1 · Accepted Answer · answered Nov 30 '18 at 08:59

1

If you use .RowSource to fill your ListBox you cannot remove list items from the list.

Therefore you need to fill your list using LstWSource.AddItem. Loop through your range and add each item to your ListBox based on your criteria. Also see Adding items in a Listbox with multiple columns.

answered Nov 30 '18 at 08:59

Pᴇʜ

56,719
10
49
73

Thanks for your answer @PEH – botakelymg Nov 30 '18 at 09:06
I do the checking. – botakelymg Nov 30 '18 at 10:42

score 0 · Answer 2 · answered Nov 30 '18 at 16:04

For additional information and bring my definite solution related to slowing populating : I used once the read of Range cells to populate a dynamic array. Then, I use this array to exclude or include lines in another second array.

These are lines to be considered :

J = 1
For i = LBound(G_varListYears) To UBound(G_varListYears)

If G_varListYears(i) <= CStr(Year(G_datJourTraitee)) Then
  '>>>
  ReDim Preserve vListTMP(J)
  vListTMP(J) = G_varListYears(i)
  J = J + 1
  '>>
ElseIf (Me.chkNextYear = True) And ((Month(G_datJourTraitee) = 11 Or Month(G_datJourTraitee) = 12) And (G_varListYears(i) = CStr(Year(G_datJourTraitee) + 1))) Then
  '>>>
  ReDim Preserve vListTMP(J)
  vListTMP(J) = G_varListYears(i)
  J = J + 1
  '>>
Else
  '>>
End If

Next i

With Me.LstWSource
  .ColumnWidths = "28pt"
  .List = vListTMP()
  .ListIndex = -1
End With '>>>

Hope it'll help too.

Remove Item from Excel Listbox

2 Answers2