Search code examples
excelvbauserform

Remove items from userform and spreadsheet at the same time

Im trying to remove items in both userform and spreadsheet but when i click the remove button, the last item(paper transfer file(yellow)) is removed in userform but it's still in spreadsheet. Not sure what goes wrong.

items in userform

1

items removed in userform

2

last item remained in sheet

3

please see below for the code.

    Dim s      As Long
    Dim ws     As Worksheet
    Set ws = ThisWorkbook.Sheets("Data")
    Dim selectedItems As New Collection

   For s = s To Me.ListboxResult.ListCount - 1
            If Me.ListboxResult.Selected(s) Then
                selectedItems.Add s
            End If
        Next s

    For s = selectedItems.Count To 1 Step -1
        Me.ListboxResult.RemoveItem selectedItems(s)
        ws.Rows(selectedItems(s) + 4).Delete
        MsgBox "The selected item(s) Is/are successfully removed.", vbOKOnly, "Removal Successful"
    Next s
    
    'Uncheck the checkbox if the listbox is empty
    If Me.ListboxResult.ListCount = 0 Then
        Me.checkboxSelect.Value = False
        MsgBox "The selected item(s) Is/are successfully removed.", vbOKOnly, "Removal Successful"
    End If

I have this code below

    For Each Cell In rng
        With Cell
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        End With
    Next Cell

I expect the item is removed in both userform and spreadsheet

Solution

  • Delete rows as you find them selected.

    Option Explicit

Private Sub CommandButton1_Click()
    Dim rng As Range, i As Long, c As Range
    Dim colItem As Long, sItem As String
    
    colItem = 1 ' 2nd column in listbox
    Set rng = ThisWorkbook.Sheets("Data").Range("E:E")
    With ListBoxResult
        For i = .ListCount To 1 Step -1
            If .Selected(i - 1) Then
            
                sItem = .List(i - 1, colItem)
                Set c = rng.Find(sItem, LookIn:=xlValues, lookat:=xlWhole)
                If Not c Is Nothing Then
                     c.EntireRow.Delete
                     Set c = Nothing
                Else
                     MsgBox sItem & " not found on sheet", vbExclamation
                End If
                .RemoveItem i - 1
            End If
        Next

    End With
End Sub