CODEWITHSUNDEEP
excelvba
kruk22
kruk22

Reputation: 159

Remove Item in a Listbox by selecting in VBA

I have a list box which is based on the worksheet range. I put a button included in my Userform that deletes the selected item inside my listbox whenever it is clicked. And everytime there is a deleted item there, the deleted item should also be removed in my Worksheet range. Here's my code in removing the item in the listbox: 

Private Sub btnRemove_Click()
Dim i As Integer

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        ListBox1.RemoveItem (i)
    End If
Next i
End Sub

How to remove the deleted item in the worksheet range? Thank you! :)

Upvotes: 2

Views: 59045

Answers (2)

SomeBuddy
SomeBuddy

Reputation: 21

Works fine without the "-1" for whatever reason.

Private Sub btnRemove_Click()
Dim i As Integer

For i = 0 To ListBox1.ListCount 
    If ListBox1.Selected(i) Then
        ListBox1.RemoveItem (i)
    End If
Next i
End Sub

Upvotes: 2

user3598756
user3598756

Reputation: 29421

since you're using .RemoveItem() method you must have set your listbox range via its List property and not with RowSource one

then you could use this code in your userform code pane:

Option Explicit

Dim listRng As Range '<-- declare a Userform scoped variable of 'Range' type to track the range whose content you fill your ListBox1 with (for instance, in Userform_Initialize())

Private Sub btnRemove_Click()
    Dim i As Long
    Dim rowsList As String

    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.Selected(i) Then
            ListBox1.RemoveItem i
        Else
            rowsList = rowsList & i + 1 & " " '<-- update the rows to be confirmed
        End If
    Next i
    If rowsList <> "" Then UpdateListRange Left(rowsList, Len(rowsList) - 1)
End Sub

Sub UpdateListRange (rowsList As String)
    Dim addr As String
    Dim iRow As Variant
    Dim rowsListArr As Variant

    rowsListArr = Split(rowsList)
    For iRow = UBound(rowsListArr) To LBound(rowsListArr) Step -1
        addr = addr & listRng(rowsListArr(iRow)).address(False, False) & ","
    Next iRow

    If addr <> "" Then addr = Left(addr, Len(addr) - 1)
    Set listRng = listRng.Parent.Range(addr)
End Sub

Private Sub UserForm_Initialize()
    With Worksheets("List").Range("C32:C41") '<-- change "List" to your actual worksheet name with the range whose content you fill ListBox1 list with
        Me.ListBox1.List = Application.Transpose(.Cells) '<-- fill ListBox1 list
        Set listRng = .Cells '<-- set the userform scoped range variable to track the "listbox filling" range
    End With
End Sub

Upvotes: 3

Related Questions