VBA to delete text after multiple carriage return (line break)

[Chipperzs]

I have VBA code on the Worksheet that records When, Who, and What Cell is changed when someone updates information in a row on the workbook spreadsheet. The code works well. I'm looking to start recording additional changes to each row, not just the last change. I've figured out how to use the carriage return (line break) [& Chr(13) & Chr(10) &] to have the information added to the top of the information already within each cell. That code works great as well. Now I would like to run some VBA on the information in each cell and remove any information after multiple carriages returns. I'd like to be able to adjust that number as needed, i.e. 2 lines, 3 lines or 5 lines.

Thanks for your comments and help in advance.

Here is what the code looks like:

Function Remove_Number(Text As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
Remove_Number = .Replace(Text, "")
End With
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
' This code identifies the Date, Time, Who, and Cell location of a change on the worksheet.

'If the change occures on two or more rows/columns [>=2 row/column : will not trigger action if update isn't in the first row/column] then there is no update identifying the change
'I.E. if you copy something into 2 or more cells, then no documentation of the change occures.
If Target.Columns.Count >= 2 Or Target.Rows.Count >= 2 Then Exit Sub
    
    'If you want to limit the area where the the change ocurres, you can use a Range.  If the change does not occures in the range ["B2:BZ600"] then there is no update identifying the change
    If Not Intersect(Target, Range("c2:BZ1000")) Is Nothing Then
        
        'This set the format of the target column.  The target column is where the change note is entered.
        'This doesn't seem to make a difference though...
        Cells(Target.Row, "A").NumberFormat = "YYYYMMDD-hh:mm:ss"
        
        'this enters the Date, Time, Who, and identifies the cell that was changed, into the identified column of the row that experenced and update
        'Format(Now, "YYYYMMDD-hh:mm:ss" gives the date and time in a formate that is easily sorted from Z-A to see the latest updates at the top
        'Application.UserName gives the Windows User ID of who made the change
        'Target.Address(0, 0) gives the address of where the change was made
        If Cells(Target.Row, "A").Value = "" Then
        Cells(Target.Row, "A").Value = Format(Now, "YYYYMMDD-hh:mm:ss") & ": " & Application.UserName & " " & Remove_Number(Target.Address(0, 0))
        
        ElseIf Cells(Target.Row, "A").Value <> "" Then
        Cells(Target.Row, "A").Value = Format(Now, "YYYYMMDD-hh:mm:ss") & ": " & Application.UserName & " " & Remove_Number(Target.Address(0, 0)) & "," & Chr(13) & Chr(10) & Cells(Target.Row, "A")
        
        End If
        
        'this enters and appends (adds) which cell(s) were updated per day.  If cells in the affected row are updated on a new day the record of updated cells will be reset
        If Cells(Target.Row, "B").Value = "" Then
        Cells(Target.Row, "B").Value = Remove_Number(Target.Address(0, 0)) & ","
        
        ElseIf Cells(Target.Row, "B").Value <> "" Then
        Cells(Target.Row, "B").Value = Remove_Number(Target.Address(0, 0)) & "," & Chr(13) & Chr(10) & Cells(Target.Row, "B")
        
        End If
        'This enters the same information in a location where it's easily identifiable who made the last change before saving the document
        Range("A1").Value = "Notes: (Updated: " & Format(Now, "YYYYMMDD-hh:mm:ss") & ") " & Application.UserName & " " & Target.Address(0, 0)
    End If

End Sub

 

[Chipperzs]

I updated the VBA code to include an action for column B where the changes are recorded as [Column&Row], and when you click on the cell in column B, it selects the cell addresses that have recorded changes. It might be helpful to someone who has multiple people accessing the same spreadsheet making changes and you want a quick way of recording who made the changes, when they made them, and which cells they changed.

I would still like to figure out a way to delete the recorded entries after a determined number (specified by me) in the code (i.e. 2, 3 or 5 entries)

Column A has entries recorded on a new line inserted at the beginning of the cell's value with a carriage return.
Column B has entries recorded at the beginning of the cell's value with a comma and space ", " after the first entry.

Function Remove_Number(Text As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
Remove_Number = .Replace(Text, "")
End With
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
' This code identifies the Date, Time, Who, and Cell location of a change on the worksheet.

'If the change occures on two or more rows/columns [>=2 row/column : will not trigger action if update isn't in the first row/column] then there is no update identifying the change
'I.E. if you copy something into 2 or more cells, then no documentation of the change occures.
If Target.Columns.Count >= 2 Or Target.Rows.Count >= 2 Then Exit Sub
    
    'If you want to limit the area where the the change ocurres, you can use a Range.  If the change does not occures in the range ["B2:BZ600"] then there is no update identifying the change
    If Not Intersect(Target, Range("c2:BZ1000")) Is Nothing Then
        
        'This set the format of the target column.  The target column is where the change note is entered.
        'This doesn't seem to make a difference though...
        Cells(Target.Row, "A").NumberFormat = "YYYYMMDD-hh:mm:ss"
        
        'this enters the Date, Time, Who, and identifies the cell that was changed, into the identified column of the row that experenced and update
        'Format(Now, "YYYYMMDD-hh:mm:ss" gives the date and time in a formate that is easily sorted from Z-A to see the latest updates at the top
        'Application.UserName gives the Windows User ID of who made the change
        'Target.Address(0, 0) gives the address of where the change was made
        If Cells(Target.Row, "A").Value = "" Then
        Cells(Target.Row, "A").Value = Format(Now, "YYYYMMDD-hh:mm:ss") & ": " & Application.UserName & " " & Remove_Number(Target.Address(0, 0))
        
        ElseIf Cells(Target.Row, "A").Value <> "" Then
        Cells(Target.Row, "A").Value = Format(Now, "YYYYMMDD-hh:mm:ss") & ": " & Application.UserName & " " & Remove_Number(Target.Address(0, 0)) & "," & Chr(13) & Chr(10) & Cells(Target.Row, "A")
        
        End If
        
        'this enters and appends (adds) which cell(s) were updated per day.  If cells in the affected row are updated on a new day the record of updated cells will be reset
        If Cells(Target.Row, "B").Value = "" Then
        Cells(Target.Row, "B").Value = Target.Address(0, 0)
        
        ElseIf Cells(Target.Row, "B").Value <> "" Then
        Cells(Target.Row, "B").Value = Target.Address(0, 0) & "," & Cells(Target.Row, "B")
        
        End If
        'This enters the same information in a location where it's easily identifiable who made the last change before saving the document
        Range("A1").Value = "Notes: (Updated: " & Format(Now, "YYYYMMDD-hh:mm:ss") & ") " & Application.UserName & " " & Target.Address(0, 0)
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 2 Then
With Target
Range(Cells(Target.Row, "B").Value).Select
End With
End If
End Sub