Create multiple rows of data from one Userform

[daithiboy]

Hi Guys,

I'll do my utmost to explain clearly and hopefully one of you excel mages can help me!

I have a userform in which audit data is input. At the top of the userform are combo and text boxes for selecting "Department", "Area" and inputting the date.

Below that are some rows of labels and text boxes for inputting results and the auditor's name.

I'll do my best to show the layout with a table

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD] ******* (Combo)[/TD]
[TD]Counters (Combo)[/TD]
[TD]Date (text box)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Standard 1 (Label)[/TD]
[TD]Result 1 (Text box)[/TD]
[TD]Auditor Name (Text box)[/TD]
[/TR]
[TR]
[TD]Standard 2 (Label)[/TD]
[TD]Result 2 (Text box)[/TD]
[TD]Auditor Name (Text box)[/TD]
[/TR]
[TR]
[TD]Standard 3 (Label)[/TD]
[TD]Result 3 (Text box)[/TD]
[TD]Auditor Name (Text box)[/TD]
[/TR]
</tbody>[/TABLE]

I would like for a command button to write to the next available row and include as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Department[/TD]
[TD]Area[/TD]
[TD]Element[/TD]
[TD]Result[/TD]
[TD]Auditor[/TD]
[/TR]
[TR]
[TD]11/11/11[/TD]
[TD]*******[/TD]
[TD]Counters[/TD]
[TD]Standard 1[/TD]
[TD]95%[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]11/11/11[/TD]
[TD]*******[/TD]
[TD]Counters[/TD]
[TD]Standard 2[/TD]
[TD]75%[/TD]
[TD]Tim[/TD]
[/TR]
[TR]
[TD]11/11/11[/TD]
[TD]*******[/TD]
[TD]Counters[/TD]
[TD]Standard 3[/TD]
[TD]90%[/TD]
[TD]John[/TD]
[/TR]
</tbody>[/TABLE]

So the data from combo boxes and date box at the top of the Userform remain the same (Column A, B & C) but the next three columns change dependent on what is input.

I hope that makes sense and please let me know if you need clarification on anything.

Cheers,
Dave

 

[Tinbendr]

Maybe something like this.

Private Sub CommandButton1_Click()
'Name the data Fields lblStd1, txtRst1, txtAuditor1
'                     lblStd2, txtRst2, txtAuditor2
'                     lblStd3, txtRst3, txtAuditor3
Dim WS As Worksheet
Dim LastRow As Long
Dim A As Long, B As Long
Set WS = Worksheets(1)

With WS
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
End With


For A = 1 To 3
    'Something has to be in Result Field to process.
    If Me.Controls("txtRst" & A) <> "" Then
        WS.Range("A" & LastRow) = Format(Me.TextBox1, "mm/dd/yy")
        WS.Range("B" & LastRow) = Me.ComboBox1
        WS.Range("C" & LastRow) = Me.ComboBox2
        WS.Cells(LastRow, A + 3) = Me.Controls("lblStd" & A).Caption
        WS.Cells(LastRow, A + 4) = Me.Controls("txtRst" & A)
        WS.Cells(LastRow, A + 4) = Me.Controls("txtAuditor" & A)
        LastRow = LastRow + 1
    End If
Next
End Sub

 

[daithiboy]

Hey Tinbendr,

Apologies for the delay, I was away for a bit.

I'm having a little trouble with this line in your code:

If Me.Controls("txtRst" & A) <> "" Then

I'm not sure where "txtRst" relates to in this line?

 

[Norie]

Dave

What are the names of the controls on the userform?

 

[daithiboy]

Hi Norie,

There are two combo boxes, a number of text boxes, labels for each and a command button to send everything to the sheet.

 

[Norie]

What are the 2 comboboxes, the texboxes and labels called?

In the code Tinbender posted they've used some assumed names - see this comment.

'Name the data Fields lblStd1, txtRst1, txtAuditor1
' lblStd2, txtRst2, txtAuditor2
' lblStd3, txtRst3, txtAuditor3

 

[daithiboy]

I've got it! Thanks for your prompt Norie. I hadn't used the same naming convention as suggested by Tinbendr and forgot to change a couple of things. Silly mistake!

One final thing that you may be able to help me with, how can I tell it to format the result textboxes as percentage??

 

[Tinbendr]

For instance:

WS.Cells(LastRow, A + 4) = Format(Me.Controls("txtRst" & A, "0.0%"))

 

[daithiboy]

Thanks guys. Really appreciated.

Keep it up!

Dave

 

[daithiboy]

Sorry guys. I tried this and got a compile error on that updated line from Tinbendr.

Error reads:

"Wrong number of arguments or improper property assignment"