jeudi 16 novembre 2023

checkbox issue when removing items

Im having this issue when checkbox moves up a row when items are removed. How could I ensure checkbox is not moving up or duplicated? because now I have to check if checkbox is duplicated manually which is not an ideal solution.
Here's the code for removing items for reference.

Dim n, s   As Long
Dim bSelected As Boolean
Dim Cell, c As Range
Dim rng    As Range: Set rng = Range("A27:H42")
Dim ws     As Worksheet
Dim colItem As Long, sItem As String
Set ws = ThisWorkbook.Sheets("Data")
Dim selectedItems As New Collection
Set SelectedItem = New Collection

For n = 0 To Me.ListboxResult.ListCount - 1
    If Me.ListboxResult.Selected(n) Then
        bSelected = True
        Exit For
    End If
Next n

If Not bSelected Then
    MsgBox "Please select an item to remove.", vbOKOnly, "Selection Required"
    Exit Sub
End If

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

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
                Exit Sub
            End If
            .RemoveItem i - 1
        End If
    Next
End With



Aucun commentaire:

Enregistrer un commentaire