excel 通过在VBA中选择删除列表框中的项

uidvcgyl  于 2023-03-31  发布在  其他
关注(0)|答案(2)|浏览(466)

我有一个基于工作表范围的列表框。我在Userform中放置了一个按钮,每当单击该按钮时,该按钮就会删除列表框中的选定项目。每次有删除的项目时,删除的项目也应该在我的工作表范围中删除以下是删除列表框中项目的代码:

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

如何删除工作表区域中已删除的项目?谢谢!:)

0md85ypi

0md85ypi1#

由于您使用的是.RemoveItem()方法,因此必须通过List属性设置列表框范围,而不是使用RowSource方法
然后你可以在你的userform代码窗格中使用以下代码:

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
ztmd8pv5

ztmd8pv52#

无论出于何种原因,没有“-1”都可以正常工作。

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

相关问题