如何更新列表框中同一列下具有相同值的多个选定行

Et Andrea 发表于 Dev

和安德里亚

在此处输入图片说明你好我是VBA的新手

我有一个具有多列数据的listbox1。每次单击删除按钮时，我都希望它为每个数据填充“状态”列下的.cell（第8行），以“丢弃”。我已经完成了以下代码。我可以选择多个行，但是在所选行中，只有一行可以更改其状态。请帮忙。谢谢。

Private Sub DeleteCartonButton_Click()

 Dim cl     As Range
 Dim rIds   As Range
 Dim Rw     As Long
 Dim String1 As String
 Dim i As Long, msg As String, Check As String

    'Generate a list of the selected items
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                msg = msg & .List(i) & vbNewLine



                String1 = Menu.ListBox1.List(Menu.ListBox1.ListIndex, 0)

                With Sheet1
                   Set rIds = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
                   With rIds
                       Set cl = .Find(String1, LookIn:=xlValues)
                       If Not cl Is Nothing Then Rw = cl.Row
                   End With                                   
                   .Cells(Rw, 8).Value = "Discarded"   
                End With
                UserForm_Initialize
            End If
        Next i
    End With

    If msg = vbNullString Then
        'If nothing was selected, tell user and let them try again
        MsgBox "No Carton was selected. Please make a selection."
        Exit Sub
    End If

End Sub

L42

根据您的评论，尝试以下操作：

'~~> get all the selected indexes
Dim myindex, index     

With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            If Not IsArray(myindex) Then
                myindex = Array(i)
            Else
                ReDim Preserve myindex(UBound(myindex) + 1)
                myindex(UBound(myindex)) = i
            End If
        End If
    Next
End With

If IsEmpty(myindex) Then Exit Sub '~~> if nothing is selected
'~~> update the sheet
With Sheet1
    For Each index In myindex
        .Range("H2").Offset(index,0).Value = "Discarded"
    Next
End With

'~~> update the ListBox1 display
DoEvents
Listbox1.RowSource = "'[WorkbookName]SheetName'!RangeAddress"