Add Text to Textbox value by looping data
 

Hello.

Thanks for answer.

I put a watch on the newtext variable and this updates correctly as the code
loops the cells. However when it comes to the last line of the code. "tb.Text
= newtext" nothing happens. The Text box remains the same value as it
started. However no errors come on screen.

Any further help much appreciated.

by the way: thanks for good examples with set variables. I will take on
board for future code.

Francis.


"Greg Wilson" wrote:

Is this what you're looking for? I added the Chr(10) for readability of the
text box text. Minimal testing.

Regards,
Greg

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox

ActiveWorkbook.Save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C8").Resize(finalrow - 7, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text

For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
start = c.Offset(, 4).Value
length = c.Offset(, 6).Value
newtext = newtext & Chr(10) & "" & name & " was in " & c.Value & _
" for " & length & " at " & start & " on " & Date & "."
Next c
tb.Text = newtext
End Sub



"Francis Brown" wrote:

Hello

I Currently have the code at the end of message.

it loops each cell in the c column in a work sheet.
takes the current textbox value and stores in a variable.
It then concatenates this to a sentance from with other variables on the
same row.
saves back to the text box and then starts again through all the iterations.
The Problem I am having is that the outputed text in the text box only has
the values from the first itteration or loop.

Could someone please explain what i'm doing wrong.

Thanks in Advance.

Code -
Dim name As String
Dim Datee As String
Dim start As String
Dim length As String
Dim newtext As String
Dim currenttext As String
ActiveWorkbook.save
finalrow = Worksheets("Adherancebycriteria").Range("c65536"). End(xlUp).Row
For Each code In
Worksheets("Adherancebycriteria").Range("c8").Resi ze(finalrow - 7, 1)
name = code.Offset(, -2).Value
Datee = code.Offset(, 3).Value
start = code.Offset(, 4).Value
length = code.Offset(, 6).Value
currenttext = Worksheets("Time Utilisation").TextBoxes("Text Box 2").Text
newtext = currenttext & "" & name & " was in " & code.Value & " for " &
length & " at " & start & " on " & Date & "."
Worksheets("Time Utilisation").TextBoxes("Text Box 2").Text = newtext
Next code

Add Text to Textbox value by looping data
 

Answer found now. see earlier thread with same subject

"Francis Brown" wrote:

Hello.

Thanks for answer.

I put a watch on the newtext variable and this updates correctly as the code
loops the cells. However when it comes to the last line of the code. "tb.Text
= newtext" nothing happens. The Text box remains the same value as it
started. However no errors come on screen.

Any further help much appreciated.

by the way: thanks for good examples with set variables. I will take on
board for future code.

Francis.


"Greg Wilson" wrote:

Is this what you're looking for? I added the Chr(10) for readability of the
text box text. Minimal testing.

Regards,
Greg

Sub Test()
Dim name As String, datee As String
Dim start As String, length As String, newtext As String
Dim finalrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range, c As Range
Dim tb As TextBox

ActiveWorkbook.Save

Set ws1 = Sheets("Adherancebycriteria")
Set ws2 = Sheets("Time Utilization")
finalrow = ws1.Range("c65536").End(xlUp).Row
Set rng = ws1.Range("C8").Resize(finalrow - 7, 1)
Set tb = ws2.TextBoxes("Text Box 2")
newtext = tb.Text

For Each c In rng.Cells
name = c.Offset(, -2).Value
datee = c.Offset(, 3).Value
start = c.Offset(, 4).Value
length = c.Offset(, 6).Value
newtext = newtext & Chr(10) & "" & name & " was in " & c.Value & _
" for " & length & " at " & start & " on " & Date & "."
Next c
tb.Text = newtext
End Sub



"Francis Brown" wrote:

Hello

I Currently have the code at the end of message.

it loops each cell in the c column in a work sheet.
takes the current textbox value and stores in a variable.
It then concatenates this to a sentance from with other variables on the
same row.
saves back to the text box and then starts again through all the iterations.
The Problem I am having is that the outputed text in the text box only has
the values from the first itteration or loop.

Could someone please explain what i'm doing wrong.

Thanks in Advance.

Code -
Dim name As String
Dim Datee As String
Dim start As String
Dim length As String
Dim newtext As String
Dim currenttext As String
ActiveWorkbook.save
finalrow = Worksheets("Adherancebycriteria").Range("c65536"). End(xlUp).Row
For Each code In
Worksheets("Adherancebycriteria").Range("c8").Resi ze(finalrow - 7, 1)
name = code.Offset(, -2).Value
Datee = code.Offset(, 3).Value
start = code.Offset(, 4).Value
length = code.Offset(, 6).Value
currenttext = Worksheets("Time Utilisation").TextBoxes("Text Box 2").Text
newtext = currenttext & "" & name & " was in " & code.Value & " for " &
length & " at " & start & " on " & Date & "."
Worksheets("Time Utilisation").TextBoxes("Text Box 2").Text = newtext
Next code