Put multi-line textbox into multiple cells

GreasySpot · Apr 5th 2016

I'm not sure how to phrase my question so that everyone understands. I want to take each line of a multi-input textbox and put each line into its own cell. Notice the form below. Each item in "Gross Weights" needs to be paired with all the other items to form the rows on the left. My code for this is below but it doesn't separate the items. It just creates a multi line cell.

[ATTACH=CONFIG]68762[/ATTACH]

Code

Private Sub CommandEnter_Click()
    Dim LastRow As Range
    Dim Table1 As ListObject
    
    'Add row to bottom of Table1
    ActiveSheet.ListObjects("Table1").ListRows.Add
    
    'Enter data from form into new row
    Set Table1 = ActiveSheet.ListObjects("Table1")
    Set LastRow = Table1.ListRows(Table1.ListRows.Count).Range
    
    With LastRow
        
        .Cells(1, 1) = MonthView1.Value
        .Cells(1, 2) = TareBox.Value
        .Cells(1, 3) = TextBox_Gross.Value
        '.Cells(1, 4) = MonthView1.Value
        .Cells(1, 5) = HaulerCombo.Value
        .Cells(1, 6) = TruckCombo.Value
        .Cells(1, 7) = PlantCombo.Value
        .Cells(1, 8) = MaterialCombo.Value
    
    End With
    
End Sub

Display More

Grimes0332 · Apr 5th 2016

Re: Put multi-line textbox into multiple cells

Assuming you mean that TextBox_Gross is a multiline textbox and you want to add a row for each line in the textbox using the same values from the other controls?

You can simply split the textbox contents to a variant array and then loop each element. You need to find out what NewLine character is used. This can be Chr(10), Chr(13) or Chr(10) & Chr(13) - one of them will work in the following. The VBA constants for these are used in the code

Code

Private Sub CommandEnter_Click() 
    Dim LastRow As Range 
    Dim Table1 As ListObject 
    
    Dim vItem as Variant
    Dim iTemp as Integer
 
     'Enter data from form into new row
    Set Table1 = ActiveSheet.ListObjects("Table1") 


    vItem = Split(TextBox_Gross.Text, vbCr)   ' OR vbLf or vbCrLf


    For iTemp = lBound(vItem) to uBound(vItem)
         'Add row to bottom of Table1
        ActiveSheet.ListObjects("Table1").ListRows.Add 
     
        Set LastRow = Table1.ListRows(Table1.ListRows.Count).Range 
     
        With LastRow 
         
            .Cells(1, 1) = MonthView1.Value 
            .Cells(1, 2) = TareBox.Value 
            .Cells(1, 3) = vItem(iTemp) 
            '.Cells(1, 4) = MonthView1.Value
            .Cells(1, 5) = HaulerCombo.Value 
            .Cells(1, 6) = TruckCombo.Value 
            .Cells(1, 7) = PlantCombo.Value 
            .Cells(1, 8) = MaterialCombo.Value 
         
        End With 


    Next
     
End Sub

Display More

GreasySpot · Apr 5th 2016

Re: Put multi-line textbox into multiple cells

Thanks for the quick reply and I do follow most of it.
I assume this

"You need to find out what NewLine character is used. This can be Chr(10, Chr(13) or Chr(10) & Chr(13)"

and

"Split(TextBox_Gross.Text, vbCr) ' OR vbLf or vbCrLf"

are what you are referring to. I'm just not sure how I'm supposed to check that?

If I put in a value of

123
456
789

in the inputbox the watch window shows a value of "123 456 789", but when I click on the value in the watch window it changes to "123456789". Also I noticed it lists it as a variant/string, I will be needing it as a number and not text.

I apologize for being so lost.

GreasySpot · Apr 5th 2016

Re: Put multi-line textbox into multiple cells

Doh! I guess I should have tried each of them before complaining I didn't know how to test them. vbCrLf seems to do the job. Thanks a million.

Grimes0332 · Apr 5th 2016

Re: Put multi-line textbox into multiple cells

Everything in a textbox is a String (TEXTbox...) but the array is declared as a Variant so should be correctly input to the worksheet as a Value. If not, then simply use the Val() function when writing to the worksheet.

Quote

the watch window shows a value of "123 456 789"

The 'space' is the Newline character. You can step through the contents of a string in the Immediate Window getting the ASCII value of each character. Paste this in the immediate window and press Enter

Code

x = "abc" & Chr(10) & "def" & Chr(10) & "ghi":for itemp = 1 to len(x):debug.Print asc(mid(x, itemp, 1)):next

The result will be:
97
98
99
10
100
101
102
10
103
104
105

If the seperator is vbCrLf then the lines showing the 10 will be preceededby a 13

To test with your textbox

Code

for iTemp = 1 to Len(TextBox_Gross.Text):Debug.Print Asc(mid(TextBox_Gross.Text, iTemp, 1)):Next

Put multi-line textbox into multiple cells

Participate now!

Share