(Solved) – Worksheet_Change – capture changes to pasted values in multiple rows


I have code on the worksheet change that will save the input value to the database. However, if the user pastes into multiple rows, only the data from one row is saved to the database. How can I get this to save the data from all the rows?

Code sample:

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("Num").Value = Num
            rs("Line").Value = Line
        End If

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

    End If
End If

    GoTo CleanUp 'skip error handler

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

End Sub```

Leave a Reply

Your email address will not be published. Required fields are marked *