Want to concatenate 10 TextBox.Values from a userform in an excel cell-specific column

KayWill

New Member
Joined
Jan 22, 2015
Messages
36
Hello,

I am requesting assistance from RoyUK; a lot of your post has helped me and I'm urgently needing your assistance...of course this is open to anyone else who can assist. I'm not an "expert" with VBA but know enough to be dangerous, which is dangerous :eek:

At any rate, I have a userform (called SHIPREQFRM), most of the data is dumping into a table (Sheet1, 'ShipDtls') but there are 5 columns (Product Type, QTY, Part No, Item Description &NOTES) and each has 10 text boxes for each so the user can add their data. I need these 5 columns to post to a secondary sheet (Sheet4, 'ADDShipDtls'). Below is the entire code string attached to a command button (to add data to the two sheets)


On Error Resume Next

' Check user input
If Me.cboREQTYPE.Value = "" Then
MsgBox "Must select type of request.", vbExclamation, "ROLLOUT, TRANSFER, etc"
Me.cboREQTYPE.SetFocus
Exit Sub
End If
If Me.cboSALESP.Value = "" Then
MsgBox "Please enter a Sales Person Name.", vbExclamation, "Select Darko Sales Person name"
Me.cboSALESP.SetFocus
Exit Sub
End If
If Me.cboCUSTNAME.Value = "" Then
MsgBox "Please select customer.", vbExclamation, "Please Select Customer in list"
Me.cboCUSTNAME.SetFocus
Exit Sub
End If
If Not IsDate(Me.REQDELVDATE.Value) Then
MsgBox "This field must contain a date.", vbExclamation, "Must provide a date, MM/DD/YY"
Me.REQDELVDATE.SetFocus
Exit Sub
End If
If Me.cboTRACKNOTICE.Value = "" Then
MsgBox "Please note who to receive tracking data."
Me.cboTRACKNOTICE.SetFocus
Exit Sub
End If
If Me.cboSHIPTO.Value = "" Then
MsgBox "Cannot leave blank, please enter address."
Me.cboSHIPTO.SetFocus
Exit Sub
End If

' Write data to worksheet
RowCount = Worksheets("ShipDtls").Range("A1").CurrentRegion.Rows.Count
With Worksheets("ShipDtls").Range("A1")
.Offset(RowCount, 0).Value = Me.DTPicker1.Value
.Offset(RowCount, 1).Value = Me.cboREQTYPE.Value
.Offset(RowCount, 2).Value = Me.JOBNO.Value
.Offset(RowCount, 3).Value = Me.cboSALESP.Value
.Offset(RowCount, 4).Value = Me.cboCUSTNAME.Value
.Offset(RowCount, 5).Value = Me.cboSTAGELOC.Value
.Offset(RowCount, 6).Value = Me.CUSTPO.Value
.Offset(RowCount, 7).Value = Me.SHIP2PO.Value
.Offset(RowCount, 8).Value = Me.cboASSIGNER.Value
.Offset(RowCount, 9).Value = DateValue(Me.REQSHDATE.Value)
.Offset(RowCount, 10).Value = DateValue(Me.REQDELVDATE.Value)
.Offset(RowCount, 11).Value = Me.REQDELVTIME.Value
.Offset(RowCount, 12).Value = Me.cboCARRIER.Value
.Offset(RowCount, 13).Value = Me.cboTRACKNOTICE.Value
.Offset(RowCount, 14).Value = Me.SPDELVNOTES.Value
.Offset(RowCount, 20).Value = Me.cboBILLTO.Value
.Offset(RowCount, 21).Value = Me.tb3PARTYACCT.Value
.Offset(RowCount, 22).Value = Me.cboSHIPTO.Value
.Offset(RowCount, 23).Value = Me.cboSHP2CTC.Value
.Offset(RowCount, 24).Value = Me.cboSHP2PHONE.Value
.Offset(RowCount, 25).Value = Me.EXPEDITOR.Value
.Offset(RowCount, 26).Value = Me.CTNSKDS.Value
.Offset(RowCount, 27).Value = Me.WGTS.Value
.Offset(RowCount, 28).Value = Me.DIMS.Value
.Offset(RowCount, 29).Value = Me.DOCKTAG.Value
.Offset(RowCount, 30).Value = Format(Now, "mm/dd/yyyy hh:nn:ss")
End With
Below is where I'm not sure is correct, doesn't give me error, just not posting anything
'add multiple row data to be inserted & combined into main table Set ws = Worksheets("ADDShipDtls")
lRow = ws.Cells(1, 2).CurrentRegion.Rows.Count + 1
With ws
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox1.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox14.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox11.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox24.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox34.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox2.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox15.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox12.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox25.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox35.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox3.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox16.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox13.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox26.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox36.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox4.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox17.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox14.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox27.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox37.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox5.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox18.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox15.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox28.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox38.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox6.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox19.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox16.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox29.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox39.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox7.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox20.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox17.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox30.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox40.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox8.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox21.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox18.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox31.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox41.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox9.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox22.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox19.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox32.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox33.Value
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox10.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox23.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox20.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox33.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox43.Value
.Cells(lRow, 6).End(xlUp).Offset(1, 0).Value = Me.DTPicker1.Value
.Cells(lRow, 7).End(xlUp).Offset(1, 0).Value = Me.cboREQTYPE.Value

End With

With Me
.ComboBox1.Value = ""
.TextBox14.Value = ""
.ComboBox11.Value = ""
.TextBox24.Value = ""
.TextBox34.Value = ""
.ComboBox2.Value = ""
.TextBox15.Value = ""
.ComboBox12.Value = ""
.TextBox25.Value = ""
.TextBox35.Value = ""
.ComboBox3.Value = ""
.TextBox16.Value = ""
.ComboBox13.Value = ""
.TextBox26.Value = ""
.TextBox36.Value = ""
.ComboBox4.Value = ""
.TextBox17.Value = ""
.ComboBox14.Value = ""
.TextBox27.Value = ""
.TextBox37.Value = ""
.ComboBox5.Value = ""
.TextBox18.Value = ""
.ComboBox15.Value = ""
.TextBox28.Value = ""
.TextBox38.Value = ""
.ComboBox6.Value = ""
.TextBox19.Value = ""
.ComboBox16.Value = ""
.TextBox29.Value = ""
.TextBox39.Value = ""
.ComboBox7.Value = ""
.TextBox20.Value = ""
.ComboBox17.Value = ""
.TextBox30.Value = ""
.TextBox40.Value = ""
.ComboBox8.Value = ""
.TextBox21.Value = ""
.ComboBox18.Value = ""
.TextBox31.Value = ""
.TextBox41.Value = ""
.ComboBox9.Value = ""
.TextBox22.Value = ""
.ComboBox19.Value = ""
.TextBox32.Value = ""
.TextBox42.Value = ""
.ComboBox10.Value = ""
.TextBox23.Value = ""
.ComboBox20.Value = ""
.TextBox33.Value = ""
.TextBox43.Value = ""

End With

' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub


I can send a sample of the workbook if necessary but any help RoyUK or anyone else can provide will be greatly appreciated.
 

KayWill

New Member
Joined
Jan 22, 2015
Messages
36
After thinking waaaay too hard, I ended up fixing this. After re-looking at the code, I saw where my mistake was. Just in case anyone would like to know, I was assigning my multiple textboxes to the same line so I fixed that and entered a Range2CSV function to properly concatenate my fields, works perfect, code change is below:


On Error Resume Next

' Check user input
If Me.cboREQTYPE.Value = "" Then
MsgBox "Must select type of request.", vbExclamation, "ROLLOUT, TRANSFER, etc"
Me.cboREQTYPE.SetFocus
Exit Sub
End If
If Me.cboSALESP.Value = "" Then
MsgBox "Please enter a Sales Person Name.", vbExclamation, "Select Darko Sales Person name"
Me.cboSALESP.SetFocus
Exit Sub
End If
If Me.cboCUSTNAME.Value = "" Then
MsgBox "Please select customer.", vbExclamation, "Please Select Customer in list"
Me.cboCUSTNAME.SetFocus
Exit Sub
End If
If Not IsDate(Me.REQDELVDATE.Value) Then
MsgBox "This field must contain a date.", vbExclamation, "Must provide a date, MM/DD/YY"
Me.REQDELVDATE.SetFocus
Exit Sub
End If
If Me.cboTRACKNOTICE.Value = "" Then
MsgBox "Please note who to receive tracking data."
Me.cboTRACKNOTICE.SetFocus
Exit Sub
End If
If Me.cboSHIPTO.Value = "" Then
MsgBox "Cannot leave blank, please enter address."
Me.cboSHIPTO.SetFocus
Exit Sub
End If

' Write data to worksheet
RowCount = Worksheets("ShipDtls").Range("A1").CurrentRegion.Rows.Count
With Worksheets("ShipDtls").Range("A1")
.Offset(RowCount, 0).Value = Me.DTPicker1.Value
.Offset(RowCount, 1).Value = Me.cboREQTYPE.Value
.Offset(RowCount, 2).Value = Me.JOBNO.Value
.Offset(RowCount, 3).Value = Me.cboSALESP.Value
.Offset(RowCount, 4).Value = Me.cboCUSTNAME.Value
.Offset(RowCount, 5).Value = Me.cboSTAGELOC.Value
.Offset(RowCount, 6).Value = Me.CUSTPO.Value
.Offset(RowCount, 7).Value = Me.SHIP2PO.Value
.Offset(RowCount, 8).Value = Me.cboASSIGNER.Value
.Offset(RowCount, 9).Value = DateValue(Me.REQSHDATE.Value)
.Offset(RowCount, 10).Value = DateValue(Me.REQDELVDATE.Value)
.Offset(RowCount, 11).Value = Me.REQDELVTIME.Value
.Offset(RowCount, 12).Value = Me.cboCARRIER.Value
.Offset(RowCount, 13).Value = Me.cboTRACKNOTICE.Value
.Offset(RowCount, 14).Value = Me.SPDELVNOTES.Value
.Offset(RowCount, 20).Value = Me.cboBILLTO.Value
.Offset(RowCount, 21).Value = Me.tb3PARTYACCT.Value
.Offset(RowCount, 22).Value = Me.cboSHIPTO.Value
.Offset(RowCount, 23).Value = Me.cboSHP2CTC.Value
.Offset(RowCount, 24).Value = Me.cboSHP2PHONE.Value
.Offset(RowCount, 25).Value = Me.EXPEDITOR.Value
.Offset(RowCount, 26).Value = Me.CTNSKDS.Value
.Offset(RowCount, 27).Value = Me.WGTS.Value
.Offset(RowCount, 28).Value = Me.DIMS.Value
.Offset(RowCount, 29).Value = Me.DOCKTAG.Value
.Offset(RowCount, 30).Value = Format(Now, "mm/dd/yyyy hh:nn:ss")
End With

'add multiple row data to be inserted & combined into a separate table which will be transferred
'to the main table using the Range2CSV function
Set ws = Worksheets("ADDShipDtls")
lRow = ws.Cells(1, 2).End(xlUp).Row + 1
With ws
.Cells(lRow, 1).End(xlUp).Offset(1, 0).Value = Me.ComboBox1.Value
.Cells(lRow, 2).End(xlUp).Offset(1, 0).Value = Me.TextBox14.Value
.Cells(lRow, 3).End(xlUp).Offset(1, 0).Value = Me.ComboBox11.Value
.Cells(lRow, 4).End(xlUp).Offset(1, 0).Value = Me.TextBox24.Value
.Cells(lRow, 5).End(xlUp).Offset(1, 0).Value = Me.TextBox34.Value
.Cells(lRow, 1).End(xlUp).Offset(2, 0).Value = Me.ComboBox2.Value
.Cells(lRow, 2).End(xlUp).Offset(2, 0).Value = Me.TextBox15.Value
.Cells(lRow, 3).End(xlUp).Offset(2, 0).Value = Me.ComboBox12.Value
.Cells(lRow, 4).End(xlUp).Offset(2, 0).Value = Me.TextBox25.Value
.Cells(lRow, 5).End(xlUp).Offset(2, 0).Value = Me.TextBox35.Value
.Cells(lRow, 1).End(xlUp).Offset(3, 0).Value = Me.ComboBox3.Value
.Cells(lRow, 2).End(xlUp).Offset(3, 0).Value = Me.TextBox16.Value
.Cells(lRow, 3).End(xlUp).Offset(3, 0).Value = Me.ComboBox13.Value
.Cells(lRow, 4).End(xlUp).Offset(3, 0).Value = Me.TextBox26.Value
.Cells(lRow, 5).End(xlUp).Offset(3, 0).Value = Me.TextBox36.Value
.Cells(lRow, 1).End(xlUp).Offset(4, 0).Value = Me.ComboBox4.Value
.Cells(lRow, 2).End(xlUp).Offset(4, 0).Value = Me.TextBox17.Value
.Cells(lRow, 3).End(xlUp).Offset(4, 0).Value = Me.ComboBox14.Value
.Cells(lRow, 4).End(xlUp).Offset(4, 0).Value = Me.TextBox27.Value
.Cells(lRow, 5).End(xlUp).Offset(4, 0).Value = Me.TextBox37.Value
.Cells(lRow, 1).End(xlUp).Offset(5, 0).Value = Me.ComboBox5.Value
.Cells(lRow, 2).End(xlUp).Offset(5, 0).Value = Me.TextBox18.Value
.Cells(lRow, 3).End(xlUp).Offset(5, 0).Value = Me.ComboBox15.Value
.Cells(lRow, 4).End(xlUp).Offset(5, 0).Value = Me.TextBox28.Value
.Cells(lRow, 5).End(xlUp).Offset(5, 0).Value = Me.TextBox38.Value
.Cells(lRow, 1).End(xlUp).Offset(6, 0).Value = Me.ComboBox6.Value
.Cells(lRow, 2).End(xlUp).Offset(6, 0).Value = Me.TextBox19.Value
.Cells(lRow, 3).End(xlUp).Offset(6, 0).Value = Me.ComboBox16.Value
.Cells(lRow, 4).End(xlUp).Offset(6, 0).Value = Me.TextBox29.Value
.Cells(lRow, 5).End(xlUp).Offset(6, 0).Value = Me.TextBox39.Value
.Cells(lRow, 1).End(xlUp).Offset(7, 0).Value = Me.ComboBox7.Value
.Cells(lRow, 2).End(xlUp).Offset(7, 0).Value = Me.TextBox20.Value
.Cells(lRow, 3).End(xlUp).Offset(7, 0).Value = Me.ComboBox17.Value
.Cells(lRow, 4).End(xlUp).Offset(7, 0).Value = Me.TextBox30.Value
.Cells(lRow, 5).End(xlUp).Offset(7, 0).Value = Me.TextBox40.Value
.Cells(lRow, 1).End(xlUp).Offset(8, 0).Value = Me.ComboBox8.Value
.Cells(lRow, 2).End(xlUp).Offset(8, 0).Value = Me.TextBox21.Value
.Cells(lRow, 3).End(xlUp).Offset(8, 0).Value = Me.ComboBox18.Value
.Cells(lRow, 4).End(xlUp).Offset(8, 0).Value = Me.TextBox31.Value
.Cells(lRow, 5).End(xlUp).Offset(8, 0).Value = Me.TextBox41.Value
.Cells(lRow, 1).End(xlUp).Offset(9, 0).Value = Me.ComboBox9.Value
.Cells(lRow, 2).End(xlUp).Offset(9, 0).Value = Me.TextBox22.Value
.Cells(lRow, 3).End(xlUp).Offset(9, 0).Value = Me.ComboBox19.Value
.Cells(lRow, 4).End(xlUp).Offset(9, 0).Value = Me.TextBox32.Value
.Cells(lRow, 5).End(xlUp).Offset(9, 0).Value = Me.TextBox33.Value
.Cells(lRow, 1).End(xlUp).Offset(10, 0).Value = Me.ComboBox10.Value
.Cells(lRow, 2).End(xlUp).Offset(10, 0).Value = Me.TextBox23.Value
.Cells(lRow, 3).End(xlUp).Offset(10, 0).Value = Me.ComboBox20.Value
.Cells(lRow, 4).End(xlUp).Offset(10, 0).Value = Me.TextBox33.Value
.Cells(lRow, 5).End(xlUp).Offset(10, 0).Value = Me.TextBox43.Value
.Cells(lRow, 6).End(xlUp).Offset(1, 0).Value = Me.DTPicker1.Value
.Cells(lRow, 7).End(xlUp).Offset(1, 0).Value = Me.cboREQTYPE.Value
End With


' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub
 

Forum statistics

Threads
1,081,528
Messages
5,359,299
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top