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

K

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 :eeek:

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.
 
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Need to add to my search worksheet
Replies
1
Views
172
craigwojo
C
P
  • Question Question
Hoping to get help with a Macro!
Replies
0
Views
309
PEM000
P
E
  • Question Question
Estimating Spreadsheet-Summing
Replies
1
Views
398
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
591
LazyBug
LazyBug
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
362
Patriot2879
P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top