excel 同时从用户表单和电子表格中删除项目

o8x7eapl 于 2023-11-20 发布在其他

关注(0)|答案(2)|浏览(109)

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

的数据
在用户表单中删除的项目

的
工作表中保留的最后一个项目

的
请看下面的代码。

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

来源：https://stackoverflow.com/questions/77492723/remove-items-from-userform-and-spreadsheet-at-the-same-time

2条答案

按热度按时间

laximzn51#

当您发现行被选中时，将其删除。

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

字符串

赞(0）回复(0）举报 2023-11-20

u1ehiz5o2#

Me.ListBox1.Selected(s)的索引是零基。paper transfer file(yellow)的索引是2。
paper transfer file(yellow)位于工作表上的单元格E7中
您需要更改代码如下。

Debug.Print selectedItems(s) + 5 ' for testing to validate the row#
ws.Rows(selectedItems(s) + 5).Delete

字符串
更新：
问题：因为每次我添加到电子表格中的项目都不相同

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

型

赞(0）回复(0）举报 2023-11-20

我来回答

excel 同时从用户表单和电子表格中删除项目

2条答案

相关问题

热门标签

最新问答