VBA code to continuously append strings to a default text in Worksheet Change

[Kopi_Blue]

Hi all, I am currently stuck on finding a way to input all new changes made to a specific row of cells into a string so that I can parse the final string into another function that drafts out an Outlook message. As there will be continuous updates made to the row, I want to be able to "write" each change made, in the form of a string, and append this string to a template string variable called "gTemp" constantly until a button has been clicked which will end the append action and call on the other function.

The changes made in the row are variables in a Date and Number format.

The logical steps can be broken down as follows:

1. Checks for updates on Row
2. Writes the Date and Number into a string
3. Append the string to the variable "gTemp"
4. Repeat Steps 1 - 3
5. When button is clicked, the other function is called upon and the variable "gTemp" in the form of a String is parsed into it

The current code I have is lacking the function described in the underlined sentence above:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s, gTemp As String
    Dim xRg_A, xRg_B, xRg_C, xRg_D As Range
    gTemp = "Hi all," & vbNewLine & vbNewLine & "The required OT support can be found below."
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    
    Set xRg_A = Intersect(Rows("58"), Target)
    Set xRg_B = Intersect(Rows("125"), Target)
    Set xRg_C = Intersect(Rows("192"), Target)
    Set xRg_D = Intersect(Rows("259"), Target)
    
        If xRg_A Is Nothing And xRg_B Is Nothing And xRg_C Is Nothing And xRg_D Is Nothing Then Exit Sub    'checks for updates in rows
        
        If IsNumeric(Target) = False Then Exit Sub  'exits the function if value input is not in a numeric form
        
        If xRg_A Is Nothing Then                    'determines which shift requires OT
            If xRg_B Is Nothing Then
                If xRg_C Is Nothing Then
                    s = "D"
                Else
                    s = "C"
                End If
            Else
                s = "B"
            End If
        Else
            s = "A"
        End If
        gTemp = gTemp & vbNewLine & vbNewLine & "Shift " & s & " has " & Target & " OT required on " & Target.EntireColumn.Cells(2).Value & "."
        Call 'other function here
 End Sub

I greatly appreciate any help on this!

 

[DanteAmor]

Hi and welcome to MrExcel.

Consider the following proposal.
Create a sheet call "Logs", in that sheet we will store all the changes made in the rows.

Assuming the following changes in sheet "Sheet1" changed (see example below).

varios 16may2020.xlsm
	A	B	C	D	E	F	G
1
57
58	100	200	3000	400
59
125		8		20
126
192	1					5
193
259	20
260
Sheet1

The code will generate something like this on the "Logs" sheet.
In column A, set a count of the number of times the row has been .
In the following cells on the right, the code puts the entered value and the number of the column (see example below).

varios 16may2020.xlsm
	A	B	C	D	E	F
1
57
58	4	100|1	200|2	3000|3	400|4
59
125	2	8|2	20|4
126
192	2	1|1	5|6
193
259	1	20|1
260
Logs

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not IsNumeric(Target.Value) Then Exit Sub
  If Not Intersect(Target, Range("58:58,125:125,192:192,259:259")) Is Nothing Then
    With Sheets("Logs")
      .Range("A" & Target.Row).Value = .Range("A" & Target.Row).Value + 1
      .Cells(Target.Row, .Range("A" & Target.Row).Value + 1).Value = Target.Value & "|" & Target.Column
    End With
  End If
End Sub

until a button has been clicked

In that button you put a code to read each row and the updated values. Then you can delete the entire sheet "logs" and start again.