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

Kopi_Blue

New Member
Joined
May 15, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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! :)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDEFG
1
57
581002003000400
59
125820
126
19215
193
25920
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
ABCDEF
1
57
584100|1200|23000|3400|4
59
12528|220|4
126
19221|15|6
193
259120|1
260
Logs


The code:
VBA 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,890
Messages
5,574,853
Members
412,623
Latest member
princexxa
Top