| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I "keep text together" within a cell to keep it on 1 line? | Excel Discussion (Misc queries) | |||
| Combine text from multiple cells into one cell - =(A1&","&A2","&A3 | Excel Worksheet Functions | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Disabling "wrap text" neuters alt-return line feeds within cell | Excel Discussion (Misc queries) | |||
| use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Hybrid Mode
