Userform help , sending data to sheet

learnig1 · Mar 31, 2022

I have 2 Combo boxes and 1 Textbox that must have data in them at top of userform
Then there are a further 6 rows of 6 textboxes , if some of the 6 rows of textboxes are left blank, when Data is sent to the sheet via the Enter Button (CommandButton2) I need to prevent the empty textboxes being loaded onto the sheet

Below is the code I have to send data to sheet

Private Sub CommandButton2_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet3")

Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

sh.Range("A" & Last_Row + 1).Value = "=Row()-1"
sh.Range("B" & Last_Row + 1).Value = Now
sh.Range("C" & Last_Row + 1).Value = UserForm5.ComboBox2.Value
sh.Range("D" & Last_Row + 1).Value = UserForm5.TextBox2.Value
sh.Range("E" & Last_Row + 1).Value = UserForm5.ComboBox1.Value
sh.Range("F" & Last_Row + 1).Value = UserForm5.TextBox3.Value
sh.Range("H" & Last_Row + 1).Value = UserForm5.TextBox5.Value
sh.Range("J" & Last_Row + 1).Value = UserForm5.TextBox7.Value
sh.Range("K" & Last_Row + 1).Value = UserForm5.TextBox8.Value
sh.Range("I" & Last_Row + 1).Value = UserForm5.TextBox6.Value
sh.Range("L" & Last_Row + 1).Value = UserForm5.TextBox10.Value

sh.Range("A" & Last_Row + 2).Value = "=Row()-1"
sh.Range("B" & Last_Row + 2).Value = Now
sh.Range("C" & Last_Row + 2).Value = UserForm5.ComboBox2.Value
sh.Range("D" & Last_Row + 2).Value = UserForm5.TextBox2.Value
sh.Range("E" & Last_Row + 2).Value = UserForm5.ComboBox1.Value
sh.Range("F" & Last_Row + 2).Value = UserForm5.TextBox11.Value
sh.Range("H" & Last_Row + 2).Value = UserForm5.TextBox15.Value
sh.Range("J" & Last_Row + 2).Value = UserForm5.TextBox19.Value
sh.Range("K" & Last_Row + 2).Value = UserForm5.TextBox23.Value
sh.Range("I" & Last_Row + 2).Value = UserForm5.TextBox27.Value
sh.Range("L" & Last_Row + 2).Value = UserForm5.TextBox31.Value

sh.Range("A" & Last_Row + 3).Value = "=Row()-1"
sh.Range("B" & Last_Row + 3).Value = Now
sh.Range("C" & Last_Row + 3).Value = UserForm5.ComboBox2.Value
sh.Range("D" & Last_Row + 3).Value = UserForm5.TextBox2.Value
sh.Range("E" & Last_Row + 3).Value = UserForm5.ComboBox1.Value
sh.Range("F" & Last_Row + 3).Value = UserForm5.TextBox12.Value
sh.Range("H" & Last_Row + 3).Value = UserForm5.TextBox16.Value
sh.Range("J" & Last_Row + 3).Value = UserForm5.TextBox20.Value
sh.Range("K" & Last_Row + 3).Value = UserForm5.TextBox24.Value
sh.Range("I" & Last_Row + 3).Value = UserForm5.TextBox28.Value
sh.Range("L" & Last_Row + 3).Value = UserForm5.TextBox32.Value

sh.Range("A" & Last_Row + 4).Value = "=Row()-1"
sh.Range("B" & Last_Row + 4).Value = Now
sh.Range("C" & Last_Row + 4).Value = UserForm5.ComboBox2.Value
sh.Range("D" & Last_Row + 4).Value = UserForm5.TextBox2.Value
sh.Range("E" & Last_Row + 4).Value = UserForm5.ComboBox1.Value
sh.Range("F" & Last_Row + 4).Value = UserForm5.TextBox13.Value
sh.Range("H" & Last_Row + 4).Value = UserForm5.TextBox17.Value
sh.Range("J" & Last_Row + 4).Value = UserForm5.TextBox21.Value
sh.Range("K" & Last_Row + 4).Value = UserForm5.TextBox25.Value
sh.Range("I" & Last_Row + 4).Value = UserForm5.TextBox29.Value
sh.Range("L" & Last_Row + 4).Value = UserForm5.TextBox33.Value

sh.Range("A" & Last_Row + 5).Value = "=Row()-1"
sh.Range("B" & Last_Row + 5).Value = Now
sh.Range("C" & Last_Row + 5).Value = UserForm5.ComboBox2.Value
sh.Range("D" & Last_Row + 5).Value = UserForm5.TextBox2.Value
sh.Range("E" & Last_Row + 5).Value = UserForm5.ComboBox1.Value
sh.Range("F" & Last_Row + 5).Value = UserForm5.TextBox14.Value
sh.Range("H" & Last_Row + 5).Value = UserForm5.TextBox18.Value
sh.Range("J" & Last_Row + 5).Value = UserForm5.TextBox22.Value
sh.Range("K" & Last_Row + 5).Value = UserForm5.TextBox26.Value
sh.Range("I" & Last_Row + 5).Value = UserForm5.TextBox30.Value
sh.Range("L" & Last_Row + 5).Value = UserForm5.TextBox34.Value

sh.Range("A" & Last_Row + 6).Value = "=Row()-1"
sh.Range("B" & Last_Row + 6).Value = Now
sh.Range("C" & Last_Row + 6).Value = UserForm5.ComboBox2.Value
sh.Range("D" & Last_Row + 6).Value = UserForm5.TextBox2.Value
sh.Range("E" & Last_Row + 6).Value = UserForm5.ComboBox1.Value
sh.Range("F" & Last_Row + 6).Value = UserForm5.TextBox35.Value
sh.Range("H" & Last_Row + 6).Value = UserForm5.TextBox36.Value
sh.Range("J" & Last_Row + 6).Value = UserForm5.TextBox37.Value
sh.Range("K" & Last_Row + 6).Value = UserForm5.TextBox38.Value
sh.Range("I" & Last_Row + 6).Value = UserForm5.TextBox39.Value
sh.Range("L" & Last_Row + 6).Value = UserForm5.TextBox40.Value

UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox3.Value = ""
UserForm5.TextBox5.Value = ""
UserForm5.TextBox6.Value = ""
UserForm5.TextBox7.Value = ""
UserForm5.TextBox8.Value = ""
UserForm5.TextBox10.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox11.Value = ""
UserForm5.TextBox15.Value = ""
UserForm5.TextBox19.Value = ""
UserForm5.TextBox23.Value = ""
UserForm5.TextBox27.Value = ""
UserForm5.TextBox31.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox12.Value = ""
UserForm5.TextBox16.Value = ""
UserForm5.TextBox20.Value = ""
UserForm5.TextBox24.Value = ""
UserForm5.TextBox28.Value = ""
UserForm5.TextBox32.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox13.Value = ""
UserForm5.TextBox17.Value = ""
UserForm5.TextBox21.Value = ""
UserForm5.TextBox25.Value = ""
UserForm5.TextBox29.Value = ""
UserForm5.TextBox33.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox14.Value = ""
UserForm5.TextBox18.Value = ""
UserForm5.TextBox22.Value = ""
UserForm5.TextBox26.Value = ""
UserForm5.TextBox30.Value = ""
UserForm5.TextBox34.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox35.Value = ""
UserForm5.TextBox36.Value = ""
UserForm5.TextBox37.Value = ""
UserForm5.TextBox38.Value = ""
UserForm5.TextBox39.Value = ""
UserForm5.TextBox40.Value = ""

Call Refresh_Data

End Sub

Snakehips · Mar 31, 2022

@learnig1 This is not tested but hopefully is of some value?

VBA Code:

Private Sub CommandButton2_Click()

'me refers to current form
If Me.ComboBox1.Value = "" Or Me.ComboBox2.Value = "" Or Me.TextBox2.Value = "" Then
MsgBox "Please ensure the key data is entered and try again."
Exit Sub
End If


Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet3")

Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

For r = 1 To 6
    sh.Range("A" & Last_Row + r).Value = "=Row()-1"
    sh.Range("B" & Last_Row + r).Value = Now
    sh.Range("C" & Last_Row + r).Value = Me.ComboBox2.Value
    sh.Range("D" & Last_Row + r).Value = Me.TextBox2.Value
    sh.Range("E" & Last_Row + r).Value = Me.ComboBox1.Value
Next r




If Me.TextBox3.Value <> "" Then sh.Range("F" & Last_Row + 1).Value = Me.TextBox3.Value
If Me.TextBox5.Value <> "" Then sh.Range("H" & Last_Row + 1).Value = Me.TextBox5.Value
If Me.TextBox7.Value <> "" Then sh.Range("J" & Last_Row + 1).Value = Me.TextBox7.Value
If Me.TextBox8.Value <> "" Then sh.Range("K" & Last_Row + 1).Value = Me.TextBox8.Value
If Me.TextBox6.Value <> "" Then sh.Range("I" & Last_Row + 1).Value = Me.TextBox6.Value
If Me.TextBox10.Value <> "" Then sh.Range("L" & Last_Row + 1).Value = Me.TextBox10.Value


'edit for Me and do similar If statements for for following ranges...

sh.Range("F" & Last_Row + 2).Value = Me.TextBox11.Value
sh.Range("H" & Last_Row + 2).Value = Me.TextBox15.Value
sh.Range("J" & Last_Row + 2).Value = Me.TextBox19.Value
sh.Range("K" & Last_Row + 2).Value = Me.TextBox23.Value
sh.Range("I" & Last_Row + 2).Value = Me.TextBox27.Value
sh.Range("L" & Last_Row + 2).Value = Me.TextBox31.Value


sh.Range("F" & Last_Row + 3).Value = UserForm5.TextBox12.Value
sh.Range("H" & Last_Row + 3).Value = UserForm5.TextBox16.Value
sh.Range("J" & Last_Row + 3).Value = UserForm5.TextBox20.Value
sh.Range("K" & Last_Row + 3).Value = UserForm5.TextBox24.Value
sh.Range("I" & Last_Row + 3).Value = UserForm5.TextBox28.Value
sh.Range("L" & Last_Row + 3).Value = UserForm5.TextBox32.Value


sh.Range("F" & Last_Row + 4).Value = UserForm5.TextBox13.Value
sh.Range("H" & Last_Row + 4).Value = UserForm5.TextBox17.Value
sh.Range("J" & Last_Row + 4).Value = UserForm5.TextBox21.Value
sh.Range("K" & Last_Row + 4).Value = UserForm5.TextBox25.Value
sh.Range("I" & Last_Row + 4).Value = UserForm5.TextBox29.Value
sh.Range("L" & Last_Row + 4).Value = UserForm5.TextBox33.Value



sh.Range("F" & Last_Row + 5).Value = UserForm5.TextBox14.Value
sh.Range("H" & Last_Row + 5).Value = UserForm5.TextBox18.Value
sh.Range("J" & Last_Row + 5).Value = UserForm5.TextBox22.Value
sh.Range("K" & Last_Row + 5).Value = UserForm5.TextBox26.Value
sh.Range("I" & Last_Row + 5).Value = UserForm5.TextBox30.Value
sh.Range("L" & Last_Row + 5).Value = UserForm5.TextBox34.Value


sh.Range("F" & Last_Row + 6).Value = UserForm5.TextBox35.Value
sh.Range("H" & Last_Row + 6).Value = UserForm5.TextBox36.Value
sh.Range("J" & Last_Row + 6).Value = UserForm5.TextBox37.Value
sh.Range("K" & Last_Row + 6).Value = UserForm5.TextBox38.Value
sh.Range("I" & Last_Row + 6).Value = UserForm5.TextBox39.Value
sh.Range("L" & Last_Row + 6).Value = UserForm5.TextBox40.Value

'edit for Me ??
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox3.Value = ""
UserForm5.TextBox5.Value = ""
UserForm5.TextBox6.Value = ""
UserForm5.TextBox7.Value = ""
UserForm5.TextBox8.Value = ""
UserForm5.TextBox10.Value = ""
UserForm5.ComboBox1.Value = ""  '<<<<< Delete this and following duplicates ?
UserForm5.ComboBox2.Value = ""  '<<<<< Delete this and following duplicates ?
UserForm5.TextBox2.Value = ""   '<<<<< Delete this and following duplicates ?
UserForm5.TextBox11.Value = ""
UserForm5.TextBox15.Value = ""
UserForm5.TextBox19.Value = ""
UserForm5.TextBox23.Value = ""
UserForm5.TextBox27.Value = ""
UserForm5.TextBox31.Value = ""
UserForm5.ComboBox1.Value = ""  '<<<. and others below
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox12.Value = ""
UserForm5.TextBox16.Value = ""
UserForm5.TextBox20.Value = ""
UserForm5.TextBox24.Value = ""
UserForm5.TextBox28.Value = ""
UserForm5.TextBox32.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox13.Value = ""
UserForm5.TextBox17.Value = ""
UserForm5.TextBox21.Value = ""
UserForm5.TextBox25.Value = ""
UserForm5.TextBox29.Value = ""
UserForm5.TextBox33.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox14.Value = ""
UserForm5.TextBox18.Value = ""
UserForm5.TextBox22.Value = ""
UserForm5.TextBox26.Value = ""
UserForm5.TextBox30.Value = ""
UserForm5.TextBox34.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox35.Value = ""
UserForm5.TextBox36.Value = ""
UserForm5.TextBox37.Value = ""
UserForm5.TextBox38.Value = ""
UserForm5.TextBox39.Value = ""
UserForm5.TextBox40.Value = ""

Call Refresh_Data


End Sub

learnig1 · Apr 3, 2022

Snakehips, Thankyou for your feedback, but found not be the solution i was looking for.

below is a clip of the userform and spreadsheet the data goes to. if the userform has values in the 2 comboboxes and the top middle texbox and values in the first 2 rows of textboxes, and the others are blank as per the pic of userfom5 ,,,, when sent to spreadsheet i get what you can see hightlighted blue and yellow. but its the rows with the blanks i dont want as there was nothing in the userform textboxes , i hope this is understandable

Snakehips · Apr 3, 2022

@learnig1 Thanks for that visual clarification.
With your six rows of data text boxes, Is it reasonable to assume that empty rows will always be from the bottom up?
Also, can an empty Bin Code text box guarantee to represent an empty row of text boxes?

learnig1 · Apr 4, 2022

Snakehips, you are corect, empty rows will always be at the bottom and yes empty Bin Code text box gurantee an empty row.
A value in the Bin Code box generate the other boxes in the row to auto populate apart for the quanities

Snakehips · Apr 4, 2022

@learnig1 Give this a try. Once again, I have not been able to test this so, hopefully I have not been careless.

VBA Code:

Private Sub CommandButton2_Click()

'me refers to current form
If Me.ComboBox1.Value = "" Or Me.ComboBox2.Value = "" Or Me.TextBox2.Value = "" Then
MsgBox "Please ensure the key data is entered and try again."
Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet3")

Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

Dim n As Integer
'determine number of rows that Contain a Bin Code
Dim n As Integer
n = (-1 * (TextBox3 <> "")) + (-1 * (TextBox11 <> "")) + (-1 * (TextBox12 <> "")) _
+ (-1 * (TextBox13 <> "")) + (-1 * (TextBox14 <> "")) + (-1 * (TextBox35 <> ""))
'quit if no rows
If n = 0 Then Exit Sub

'populate  n rows
For r = 1 To n
'ID elements
    sh.Range("A" & Last_Row + r).Value = "=Row()-1"
    sh.Range("B" & Last_Row + r).Value = Now
    sh.Range("C" & Last_Row + r).Value = Me.ComboBox2.Value
    sh.Range("D" & Last_Row + r).Value = Me.TextBox2.Value
    sh.Range("E" & Last_Row + r).Value = Me.ComboBox1.Value


'Use Select Case which is like using a series IF statements
Select Case r     'Do based upon value ( Case )of r

Case 1
sh.Range("F" & Last_Row + 1).Value = Me.TextBox3.Value
sh.Range("H" & Last_Row + 1).Value = Me.TextBox5.Value
sh.Range("J" & Last_Row + 1).Value = Me.TextBox7.Value
sh.Range("K" & Last_Row + 1).Value = Me.TextBox8.Value
sh.Range("I" & Last_Row + 1).Value = Me.TextBox6.Value
sh.Range("L" & Last_Row + 1).Value = Me.TextBox10.Value

Case 2
sh.Range("F" & Last_Row + 2).Value = Me.TextBox11.Value
sh.Range("H" & Last_Row + 2).Value = Me.TextBox15.Value
sh.Range("J" & Last_Row + 2).Value = Me.TextBox19.Value
sh.Range("K" & Last_Row + 2).Value = Me.TextBox23.Value
sh.Range("I" & Last_Row + 2).Value = Me.TextBox27.Value
sh.Range("L" & Last_Row + 2).Value = Me.TextBox31.Value

Case 3
sh.Range("F" & Last_Row + 3).Value = Me.TextBox12.Value
sh.Range("H" & Last_Row + 3).Value = Me.TextBox16.Value
sh.Range("J" & Last_Row + 3).Value = Me.TextBox20.Value
sh.Range("K" & Last_Row + 3).Value = Me.TextBox24.Value
sh.Range("I" & Last_Row + 3).Value = Me.TextBox28.Value
sh.Range("L" & Last_Row + 3).Value = Me.TextBox32.Value

Case 4
sh.Range("F" & Last_Row + 4).Value = Me.TextBox13.Value
sh.Range("H" & Last_Row + 4).Value = Me.TextBox17.Value
sh.Range("J" & Last_Row + 4).Value = Me.TextBox21.Value
sh.Range("K" & Last_Row + 4).Value = Me.TextBox25.Value
sh.Range("I" & Last_Row + 4).Value = Me.TextBox29.Value
sh.Range("L" & Last_Row + 4).Value = Me.TextBox33.Value


Case 5
sh.Range("F" & Last_Row + 5).Value = Me.TextBox14.Value
sh.Range("H" & Last_Row + 5).Value = Me.TextBox18.Value
sh.Range("J" & Last_Row + 5).Value = Me.TextBox22.Value
sh.Range("K" & Last_Row + 5).Value = Me.TextBox26.Value
sh.Range("I" & Last_Row + 5).Value = Me.TextBox30.Value
sh.Range("L" & Last_Row + 5).Value = Me.TextBox34.Value

Case 6
sh.Range("F" & Last_Row + 6).Value = Me.TextBox35.Value
sh.Range("H" & Last_Row + 6).Value = Me.TextBox36.Value
sh.Range("J" & Last_Row + 6).Value = Me.TextBox37.Value
sh.Range("K" & Last_Row + 6).Value = Me.TextBox38.Value
sh.Range("I" & Last_Row + 6).Value = Me.TextBox39.Value
sh.Range("L" & Last_Row + 6).Value = Me.TextBox40.Value

End Select

Next r


'edit for Me ??
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox3.Value = ""
UserForm5.TextBox5.Value = ""
UserForm5.TextBox6.Value = ""
UserForm5.TextBox7.Value = ""
UserForm5.TextBox8.Value = ""
UserForm5.TextBox10.Value = ""
UserForm5.ComboBox1.Value = ""  '<<<<< Delete this and following duplicates ?
UserForm5.ComboBox2.Value = ""  '<<<<< Delete this and following duplicates ?
UserForm5.TextBox2.Value = ""   '<<<<< Delete this and following duplicates ?
UserForm5.TextBox11.Value = ""
UserForm5.TextBox15.Value = ""
UserForm5.TextBox19.Value = ""
UserForm5.TextBox23.Value = ""
UserForm5.TextBox27.Value = ""
UserForm5.TextBox31.Value = ""
UserForm5.ComboBox1.Value = ""  '<<<. and others below
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox12.Value = ""
UserForm5.TextBox16.Value = ""
UserForm5.TextBox20.Value = ""
UserForm5.TextBox24.Value = ""
UserForm5.TextBox28.Value = ""
UserForm5.TextBox32.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox13.Value = ""
UserForm5.TextBox17.Value = ""
UserForm5.TextBox21.Value = ""
UserForm5.TextBox25.Value = ""
UserForm5.TextBox29.Value = ""
UserForm5.TextBox33.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox14.Value = ""
UserForm5.TextBox18.Value = ""
UserForm5.TextBox22.Value = ""
UserForm5.TextBox26.Value = ""
UserForm5.TextBox30.Value = ""
UserForm5.TextBox34.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox35.Value = ""
UserForm5.TextBox36.Value = ""
UserForm5.TextBox37.Value = ""
UserForm5.TextBox38.Value = ""
UserForm5.TextBox39.Value = ""
UserForm5.TextBox40.Value = ""

Call Refresh_Data


End Sub

Snakehips · Apr 4, 2022

?Otherwise, the simplest edit of your original code is probably.....

VBA Code:

Private Sub CommandButton2_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet3")

Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

If UserForm5.TextBox3.Value <> "" Then
    sh.Range("A" & Last_Row + 1).Value = "=Row()-1"
    sh.Range("B" & Last_Row + 1).Value = Now
    sh.Range("C" & Last_Row + 1).Value = UserForm5.ComboBox2.Value
    sh.Range("D" & Last_Row + 1).Value = UserForm5.TextBox2.Value
    sh.Range("E" & Last_Row + 1).Value = UserForm5.ComboBox1.Value
    sh.Range("F" & Last_Row + 1).Value = UserForm5.TextBox3.Value
    sh.Range("H" & Last_Row + 1).Value = UserForm5.TextBox5.Value
    sh.Range("J" & Last_Row + 1).Value = UserForm5.TextBox7.Value
    sh.Range("K" & Last_Row + 1).Value = UserForm5.TextBox8.Value
    sh.Range("I" & Last_Row + 1).Value = UserForm5.TextBox6.Value
    sh.Range("L" & Last_Row + 1).Value = UserForm5.TextBox10.Value
End If

If UserForm5.TextBox11.Value <> "" Then
    sh.Range("A" & Last_Row + 2).Value = "=Row()-1"
    sh.Range("B" & Last_Row + 2).Value = Now
    sh.Range("C" & Last_Row + 2).Value = UserForm5.ComboBox2.Value
    sh.Range("D" & Last_Row + 2).Value = UserForm5.TextBox2.Value
    sh.Range("E" & Last_Row + 2).Value = UserForm5.ComboBox1.Value
    sh.Range("F" & Last_Row + 2).Value = UserForm5.TextBox11.Value
    sh.Range("H" & Last_Row + 2).Value = UserForm5.TextBox15.Value
    sh.Range("J" & Last_Row + 2).Value = UserForm5.TextBox19.Value
    sh.Range("K" & Last_Row + 2).Value = UserForm5.TextBox23.Value
    sh.Range("I" & Last_Row + 2).Value = UserForm5.TextBox27.Value
    sh.Range("L" & Last_Row + 2).Value = UserForm5.TextBox31.Value
End If

If UserForm5.TextBox12.Value <> "" Then
    sh.Range("A" & Last_Row + 3).Value = "=Row()-1"
    sh.Range("B" & Last_Row + 3).Value = Now
    sh.Range("C" & Last_Row + 3).Value = UserForm5.ComboBox2.Value
    sh.Range("D" & Last_Row + 3).Value = UserForm5.TextBox2.Value
    sh.Range("E" & Last_Row + 3).Value = UserForm5.ComboBox1.Value
    sh.Range("F" & Last_Row + 3).Value = UserForm5.TextBox12.Value
    sh.Range("H" & Last_Row + 3).Value = UserForm5.TextBox16.Value
    sh.Range("J" & Last_Row + 3).Value = UserForm5.TextBox20.Value
    sh.Range("K" & Last_Row + 3).Value = UserForm5.TextBox24.Value
    sh.Range("I" & Last_Row + 3).Value = UserForm5.TextBox28.Value
    sh.Range("L" & Last_Row + 3).Value = UserForm5.TextBox32.Value
End If

If UserForm5.TextBox13.Value <> "" Then
    sh.Range("A" & Last_Row + 4).Value = "=Row()-1"
    sh.Range("B" & Last_Row + 4).Value = Now
    sh.Range("C" & Last_Row + 4).Value = UserForm5.ComboBox2.Value
    sh.Range("D" & Last_Row + 4).Value = UserForm5.TextBox2.Value
    sh.Range("E" & Last_Row + 4).Value = UserForm5.ComboBox1.Value
    sh.Range("F" & Last_Row + 4).Value = UserForm5.TextBox13.Value
    sh.Range("H" & Last_Row + 4).Value = UserForm5.TextBox17.Value
    sh.Range("J" & Last_Row + 4).Value = UserForm5.TextBox21.Value
    sh.Range("K" & Last_Row + 4).Value = UserForm5.TextBox25.Value
    sh.Range("I" & Last_Row + 4).Value = UserForm5.TextBox29.Value
    sh.Range("L" & Last_Row + 4).Value = UserForm5.TextBox33.Value
End If

If UserForm5.TextBox14.Value <> "" Then
    sh.Range("A" & Last_Row + 5).Value = "=Row()-1"
    sh.Range("B" & Last_Row + 5).Value = Now
    sh.Range("C" & Last_Row + 5).Value = UserForm5.ComboBox2.Value
    sh.Range("D" & Last_Row + 5).Value = UserForm5.TextBox2.Value
    sh.Range("E" & Last_Row + 5).Value = UserForm5.ComboBox1.Value
    sh.Range("F" & Last_Row + 5).Value = UserForm5.TextBox14.Value
    sh.Range("H" & Last_Row + 5).Value = UserForm5.TextBox18.Value
    sh.Range("J" & Last_Row + 5).Value = UserForm5.TextBox22.Value
    sh.Range("K" & Last_Row + 5).Value = UserForm5.TextBox26.Value
    sh.Range("I" & Last_Row + 5).Value = UserForm5.TextBox30.Value
    sh.Range("L" & Last_Row + 5).Value = UserForm5.TextBox34.Value
End If

If UserForm5.TextBox35.Value <> "" Then
    sh.Range("A" & Last_Row + 6).Value = "=Row()-1"
    sh.Range("B" & Last_Row + 6).Value = Now
    sh.Range("C" & Last_Row + 6).Value = UserForm5.ComboBox2.Value
    sh.Range("D" & Last_Row + 6).Value = UserForm5.TextBox2.Value
    sh.Range("E" & Last_Row + 6).Value = UserForm5.ComboBox1.Value
    sh.Range("F" & Last_Row + 6).Value = UserForm5.TextBox35.Value
    sh.Range("H" & Last_Row + 6).Value = UserForm5.TextBox36.Value
    sh.Range("J" & Last_Row + 6).Value = UserForm5.TextBox37.Value
    sh.Range("K" & Last_Row + 6).Value = UserForm5.TextBox38.Value
    sh.Range("I" & Last_Row + 6).Value = UserForm5.TextBox39.Value
    sh.Range("L" & Last_Row + 6).Value = UserForm5.TextBox40.Value
End If

UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox3.Value = ""
UserForm5.TextBox5.Value = ""
UserForm5.TextBox6.Value = ""
UserForm5.TextBox7.Value = ""
UserForm5.TextBox8.Value = ""
UserForm5.TextBox10.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox11.Value = ""
UserForm5.TextBox15.Value = ""
UserForm5.TextBox19.Value = ""
UserForm5.TextBox23.Value = ""
UserForm5.TextBox27.Value = ""
UserForm5.TextBox31.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox12.Value = ""
UserForm5.TextBox16.Value = ""
UserForm5.TextBox20.Value = ""
UserForm5.TextBox24.Value = ""
UserForm5.TextBox28.Value = ""
UserForm5.TextBox32.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox13.Value = ""
UserForm5.TextBox17.Value = ""
UserForm5.TextBox21.Value = ""
UserForm5.TextBox25.Value = ""
UserForm5.TextBox29.Value = ""
UserForm5.TextBox33.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox14.Value = ""
UserForm5.TextBox18.Value = ""
UserForm5.TextBox22.Value = ""
UserForm5.TextBox26.Value = ""
UserForm5.TextBox30.Value = ""
UserForm5.TextBox34.Value = ""
UserForm5.ComboBox1.Value = ""
UserForm5.ComboBox2.Value = ""
UserForm5.TextBox2.Value = ""
UserForm5.TextBox35.Value = ""
UserForm5.TextBox36.Value = ""
UserForm5.TextBox37.Value = ""
UserForm5.TextBox38.Value = ""
UserForm5.TextBox39.Value = ""
UserForm5.TextBox40.Value = ""

Call Refresh_Data


End Sub

dmt32 · Apr 4, 2022

another way to avoid hard coding all the control names would be to read them in to a 2D array

give following a try & see if helps you

1 - insert a worksheet & name it TextBoxes
2 - paste the following data to the inserted worksheet as shown

04-04-2022.xls

A

B

C

D

E

F

1

TextBox3

TextBox5

TextBox7

TextBox8

TextBox6

TextBox10

2

TextBox11

TextBox15

TextBox19

TextBox23

TextBox27

TextBox31

3

TextBox12

TextBox16

TextBox20

TextBox24

TextBox28

TextBox32

4

TextBox13

TextBox17

TextBox21

TextBox25

TextBox29

TextBox33

5

TextBox14

TextBox18

TextBox22

TextBox26

TextBox30

TextBox34

6

TextBox35

TextBox36

TextBox37

TextBox38

TextBox39

TextBox40

TextBoxes

this sheet will be automatically hidden from users.

3 - make a BACK-UP of your workbook & then delete all existing code in your userforms code page

4 - Copy all following code as published to your userform code page

VBA Code:

Dim RequiredBox(1 To 3)  As Control
Dim TextBoxes            As Variant
Dim wsSheet3             As Worksheet

Private Sub CommandButton2_Click()
    Dim arr()       As Variant, Box As Variant
    Dim boxrow(6)   As Long
    Dim r           As Long, c As Long, i As Long
    Dim entrycount  As Long, entry As Long, lastrow As Long
   
    On Error GoTo myerror
   
    For i = 1 To 3
        'validate required controls
        If Len(RequiredBox(i).Value) = 0 Then
            MsgBox Choose(i, "Name", "Job Number", "Department") & Chr(10) & _
            "Entry Required.", 48, "Required Entry": RequiredBox(i).SetFocus: Exit Sub
        End If
    Next i
   
    lastrow = wsSheet3.Cells(wsSheet3.Rows.Count, "A").End(xlUp).Row + 1
   
    'validate textbox line entries
    For r = 1 To UBound(TextBoxes, 1)
        entry = 0
        For c = 1 To UBound(TextBoxes, 2)
            If Len(Me.Controls(TextBoxes(r, c)).Value) > 0 Then entry = entry + 1
        Next c
        'line 1 must have entry
        If entry > 0 Or r = 1 Then
            If entry < UBound(TextBoxes, 2) Then
                MsgBox "Please Complete Line " & r, 48, "Not Complete"
                Me.Controls(TextBoxes(r, 1)).SetFocus
                Exit Sub
            Else
                entrycount = entrycount + 1
                boxrow(entrycount) = r
            End If
        End If
    Next r
   
    'size array
    ReDim arr(1 To entrycount, 1 To 11)
   
    For r = 1 To UBound(arr, 1)
        'required values
        For c = 1 To UBound(arr, 2)
            If c < 6 Then
                'required data
                arr(r, c) = Choose(c, "=Row()-1", Now, RequiredBox(1).Value, _
                                    RequiredBox(2).Value, RequiredBox(3).Value)
            Else
                'pass textbox values to array
                arr(r, c) = Me.Controls(TextBoxes(boxrow(r), c - 5)).Value
            End If
        Next c
    Next r
   
    'post data to worksheet
    wsSheet3.Cells(lastrow, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
   
myerror:
    'inform user
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error" Else _
    MsgBox "Record Completed.", 64, "Complete": Call Refresh_Data
   
End Sub

Sub Refresh_Data()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
        Case "TextBox", "ComboBox"
            ctrl.Value = ""
        End Select
    Next ctrl
    RequiredBox(1).SetFocus
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
   
    With ThisWorkbook
        Set wsSheet3 = .Worksheets("Sheet3")
        With .Worksheets("TextBoxes")
            TextBoxes = .Range("A1").CurrentRegion.Value2
            .Visible = xlSheetVeryHidden
        End With
    End With
   
    'required controls
    For i = 1 To 3
        Set RequiredBox(i) = Choose(i, Me.ComboBox2, Me.TextBox2, Me.ComboBox1)
    Next i
   
End Sub

I have only lightly tested but code should require controls for Name, Job Number, Department & the first line of textboxes to be completed before data can be submitted. Code should also, ignore any subsequent blank textbox rows on the userform but will require a line that has been started, to be completed.

Hope helpful

Dave

learnig1 · Apr 5, 2022

Snakehips,,,,thank you very much,,, youe recent first option workd well..
Now i can put in to further testing in a working enviroment, no doubt there could be a few additions found to make it even better

Userform help , sending data to sheet

learnig1

New Member

Excel Facts

Snakehips

Well-known Member

learnig1

New Member

Snakehips

Well-known Member

learnig1

New Member

Snakehips

Well-known Member

Snakehips

Well-known Member

dmt32

Well-known Member

learnig1

New Member

Similar threads

Forum statistics

Share this page

Userform help , sending data to sheet

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock