excel - Worksheet_Change - 捕获对多行中粘贴值的更改

Question

我在工作表更改上有代码，可以将输入值保存到数据库中。但是，如果用户粘贴到多行中，则只会将一行中的数据保存到数据库中。我怎样才能得到这个来保存所有行的数据？

代码示例：

Private Sub Worksheet_Change(ByVal Target As Range)

   On Error GoTo ErrorHandler
'Exit if selected row is the header row
If (Target.Row >= 7) Then
    If Not (Intersect(Target, Columns(Sheets("PRs RFQs POs").Range("Table_v_Sourcing_Report[Comments]").Column)) Is Nothing)  Then

        Application.EnableEvents = False
        'Define variables
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim sConnString As String
        Dim CommentType As String
        Dim PO, PR, Num As String
        Dim POLine, PRLine, Line As Double

        sConnString = "database connection stuff"

        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        cn.Open sConnString

        Num = Cells(Target.Row, Range("Table_v_Sourcing_Report[Req Num]").Column).Value
        Line = Cells(Target.Row, Range("Table_v_Sourcing_Report[Req Line]").Column).Value

        rs.Open "select * from t_sourcing_comments where type = 'PR' and num = " & Num & " and line = " & Line, cn, adOpenKeyset, adLockOptimistic

        If rs.EOF Then
            rs.AddNew
            rs("Num").Value = Num
            rs("Line").Value = Line
        End If

        rs("Comment").Value = Cells(Target.Row, Range("Table_v_Sourcing_Report[Comments]").Column).Value
        rs.Update

    End If
End If

    GoTo CleanUp 'skip error handler

ErrorHandler:
    MsgBox "Failed to save comment(s):" & Err.Description
CleanUp:
    On Error Resume Next
    Application.EnableEvents = True
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub```

标签： excelvba