通过在 VBA 中选择来删除列表框中的项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40255387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove Item in a Listbox by selecting in VBA
提问by kruk22
I have a list box which is based on the worksheet range. I put a button included in my Userform that deletes the selected item inside my listbox whenever it is clicked. And everytime there is a deleted item there, the deleted item should also be removedin my Worksheet range. Here's my code in removing the item in the listbox:
我有一个基于工作表范围的列表框。我在我的用户窗体中放置了一个按钮，只要单击它，它就会删除我的列表框中的所选项目。并且每次那里有已删除的项目时，也应在我的工作表范围中删除已删除的项目。这是我删除列表框中项目的代码：
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
How to remove the deleted item in the worksheet range? Thank you! :)
如何删除工作表范围中已删除的项目？谢谢！:)
回答by user3598756
since you're using .RemoveItem()method you must have set your listbox range via its Listproperty and not with RowSourceone
由于您使用的是.RemoveItem()方法，因此您必须通过其List属性设置列表框范围，而不是使用RowSource一个
then you could use this code in your userform code pane:
那么您可以在您的用户表单代码窗格中使用此代码：
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
