Userform help , sending data to sheet

L

learnig1

New Member
Joined
Mar 31, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
@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
 
Upvote 0
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
image004.png
image005.png

image005.png
 
Upvote 0
@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?
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0
?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
 
Upvote 0
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
ABCDEF
1TextBox3TextBox5TextBox7TextBox8TextBox6TextBox10
2TextBox11TextBox15TextBox19TextBox23TextBox27TextBox31
3TextBox12TextBox16TextBox20TextBox24TextBox28TextBox32
4TextBox13TextBox17TextBox21TextBox25TextBox29TextBox33
5TextBox14TextBox18TextBox22TextBox26TextBox30TextBox34
6TextBox35TextBox36TextBox37TextBox38TextBox39TextBox40
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA code to send emails if column i = >5
Replies
7
Views
800
Joe4
Joe4
SemperFi
  • Solved
VBA Form TextBox needs formatting
Replies
1
Views
532
SemperFi
SemperFi
X
  • Solved
When update the record the entries are saved in the columnheaders ^^
Replies
2
Views
410
xainthowell
X
X
  • Solved
Displayed numeric date instead of the entered format date
Replies
6
Views
482
hicksi
hicksi
X
  • Solved
It didn't change the forecolor of the textbox
Replies
4
Views
520
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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