同时从用户窗体和电子表格中删除项目-解网

问：

我试图删除用户表单和电子表格中的项目，但是当我单击删除按钮时，最后一项（纸质传输文件（黄色））在用户表单中删除，但它仍在电子表格中。不知道出了什么问题。

用户窗体中的项目

在用户窗体中删除的项目

工作表中保留的最后一项

请参阅下面的代码。

    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

我在下面有这个代码

    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

我希望该项目在用户窗体和电子表格中都被删除

Excel VBA 用户窗体

    Dim c As Range
    For s = SelectedItems.Count To 1 Step -1
        Set c = ws.Columns("E").Find(Me.ListBox1.List(SelectedItems(s)), LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            c.EntireRow.Delete
            Set c = Nothing
            MsgBox "The selected item(s) Is/are successfully removed.", vbOKOnly, "Removal Successful"
        End If
        Me.ListboxResult.RemoveItem SelectedItems(s)
    Next s

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

同时从用户窗体和电子表格中删除项目

Remove items from userform and spreadsheet at the same time

评论

评论

评论