|
TextBox9.Text = Range("J:J") --- New line for each cell with a value?
Hello Fellow Programmers!
I am in need of some assistance with getting a text box to accept a range of cells and put the contents of each value inside the textbox on a seprate line...I have been messing around with some of the code and managed to get it to work correctly however I know there is a better code to use instead of the following... TextBox9.Value = Range("J6") & vbLf & Range("J7") & vbLf & Range("J8") & vbLf & Range("J9") & vbLf & Range("J10") & vbLf & Range("J11") & vbLf & Range("J12") My code in my dreams works like this... TextBox9.Value = Sheet1.Range("J:J") & vbLf vbLf would only apply to nonblank values...example below. J1 = Value NEW LINE (vbLf) J2 = Value NEW LINE (vbLf) J3 = Empty No Line Thanks! Hope to get some feedback... -Todd |
TextBox9.Text = Range("J:J") --- New line for each cell with a value?
Try something like
Dim Rng As Range Dim S As String For Each Rng In Application.Intersect( _ ActiveSheet.UsedRange, ActiveSheet.Columns(10)).Cells S = S & Rng.Text & vbCrLf Next Rng S = Left(S, Len(S) - 1) 'get rid of the trailing vbCrLf Me.TextBox1.Text = S Make sure the MultiLine property of the TextBox is True. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) wrote in message ups.com... Hello Fellow Programmers! I am in need of some assistance with getting a text box to accept a range of cells and put the contents of each value inside the textbox on a seprate line...I have been messing around with some of the code and managed to get it to work correctly however I know there is a better code to use instead of the following... TextBox9.Value = Range("J6") & vbLf & Range("J7") & vbLf & Range("J8") & vbLf & Range("J9") & vbLf & Range("J10") & vbLf & Range("J11") & vbLf & Range("J12") My code in my dreams works like this... TextBox9.Value = Sheet1.Range("J:J") & vbLf vbLf would only apply to nonblank values...example below. J1 = Value NEW LINE (vbLf) J2 = Value NEW LINE (vbLf) J3 = Empty No Line Thanks! Hope to get some feedback... -Todd |
TextBox9.Text = Range("J:J") --- New line for each cell with a value?
A slightly better version, that skips blanks cells, is
Dim Rng As Range Dim S As String For Each Rng In Application.Intersect( _ ActiveSheet.UsedRange, ActiveSheet.Columns(10)).Cells If Trim(Rng.Text) < vbNullString Then S = S & Rng.Text & vbCrLf End If Next Rng S = Left(S, Len(S) - 1) 'get rid of the trailing vbCrLf Me.TextBox1.Text = S -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Chip Pearson" wrote in message ... Try something like Dim Rng As Range Dim S As String For Each Rng In Application.Intersect( _ ActiveSheet.UsedRange, ActiveSheet.Columns(10)).Cells S = S & Rng.Text & vbCrLf Next Rng S = Left(S, Len(S) - 1) 'get rid of the trailing vbCrLf Me.TextBox1.Text = S Make sure the MultiLine property of the TextBox is True. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) wrote in message ups.com... Hello Fellow Programmers! I am in need of some assistance with getting a text box to accept a range of cells and put the contents of each value inside the textbox on a seprate line...I have been messing around with some of the code and managed to get it to work correctly however I know there is a better code to use instead of the following... TextBox9.Value = Range("J6") & vbLf & Range("J7") & vbLf & Range("J8") & vbLf & Range("J9") & vbLf & Range("J10") & vbLf & Range("J11") & vbLf & Range("J12") My code in my dreams works like this... TextBox9.Value = Sheet1.Range("J:J") & vbLf vbLf would only apply to nonblank values...example below. J1 = Value NEW LINE (vbLf) J2 = Value NEW LINE (vbLf) J3 = Empty No Line Thanks! Hope to get some feedback... -Todd |
TextBox9.Text = Range("J:J") --- New line for each cell with a value?
Thanks Chip! Worked great and what was even better was your no hassle
of inputting in as a piece of code! -Todd Chip Pearson wrote: Try something like Dim Rng As Range Dim S As String For Each Rng In Application.Intersect( _ ActiveSheet.UsedRange, ActiveSheet.Columns(10)).Cells S = S & Rng.Text & vbCrLf Next Rng S = Left(S, Len(S) - 1) 'get rid of the trailing vbCrLf Me.TextBox1.Text = S Make sure the MultiLine property of the TextBox is True. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) wrote in message ups.com... Hello Fellow Programmers! I am in need of some assistance with getting a text box to accept a range of cells and put the contents of each value inside the textbox on a seprate line...I have been messing around with some of the code and managed to get it to work correctly however I know there is a better code to use instead of the following... TextBox9.Value = Range("J6") & vbLf & Range("J7") & vbLf & Range("J8") & vbLf & Range("J9") & vbLf & Range("J10") & vbLf & Range("J11") & vbLf & Range("J12") My code in my dreams works like this... TextBox9.Value = Sheet1.Range("J:J") & vbLf vbLf would only apply to nonblank values...example below. J1 = Value NEW LINE (vbLf) J2 = Value NEW LINE (vbLf) J3 = Empty No Line Thanks! Hope to get some feedback... -Todd |
| All times are GMT +1. The time now is 07:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com