VBA Uerform - Transfer Data to Empty Rows/Columns

[Lindsay5590]

Hi,

I am looking for assistance with my userform/code (I am not well versed in VBA but can get by), I had a userform where I submitted data concerning each person one at a time and my code to transfer this data to a worksheet worked perfectly as I was only doing one person at a time therefore after each time I clicked "submit" - my next entry would move to a new row.

However the process has changed and now I have 10 Combobox's that will be each persons name, with an accompanying text box where a value will be entered. What I am looking to do is once the form is complete and I click submit, all the combobox entries (name) will go into Column A, and the accompanying textboxes (values) into column B. And as this will be a daily input, when I do the same the next day, it doesn't overwrite the previous days but follows on.

My code at the moment transfers all the data onto one row, then the following submission goes onto the following row, meaning my names are in columns A, C, E, G and my values are in columns B, D, F , H and so on.....

Current Code:

Private Sub submitbutton_Click()
Dim emptyRow As Long
'Make Sheet2 active
Sheet2.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
If submittedby.Value = "" Then
MsgBox "Submitted By Section Incomplete"
Else
'Transfer information
Cells(emptyRow, 1).Value = DTPicker1.Value
Cells(emptyRow, 2).Value = Name1.Value
Cells(emptyRow, 3).Value = Name1a.Value
Cells(emptyRow, 2).Value = name2.Value
Cells(emptyRow, 3).Value = name2a.Value
Cells(emptyRow, 6).Value = name3.Value
Cells(emptyRow, 7).Value = name3a.Value
Cells(emptyRow, 8).Value = name4.Value
Cells(emptyRow, 9).Value = name4a.Value
Cells(emptyRow, 10).Value = name5.Value
Cells(emptyRow, 11).Value = name5a.Value
Cells(emptyRow, 12).Value = name6.Value
Cells(emptyRow, 13).Value = name6a.Value
Cells(emptyRow, 14).Value = name7.Value
Cells(emptyRow, 15).Value = name7a.Value
Cells(emptyRow, 16).Value = name8.Value
Cells(emptyRow, 17).Value = name8a.Value
Cells(emptyRow, 18).Value = name9.Value
Cells(emptyRow, 19).Value = name9a.Value
Cells(emptyRow, 20).Value = name10.Value
Cells(emptyRow, 21).Value = name10a.Value

End If

'unclick submit button with no action (-1 removes duplication)
With submitbutton
Me.submitbutton.Value = False - 1
End With
' stay on front page
Sheet3.Activate

End Sub


Any help that you could provide would definitely be appreciated !

 

[My Aswer Is This]

What your asking for now is a lot different from the your script your showing.

You said now you want:

I click submit, all the combobox entries (name) will go into Column A, and the accompanying textboxes (values) into column B


The best way I know how to do this is like this:

Create 10 Comboboxes and name them like this:

This is the default name Excel gives Comboboxes:
ComboBox1
ComboBox2

Etc.

And create 10 TextBoxes and give them their default names

Like:

TextBox1
Textbox2

etc.

Then we can loop through those Comboboxes and TextBoxs

ComboBox1 is related to TextBox1
And so on:

Then enter this script into your Submit button

Giving ComboBoxes names like "George" and "Mary" and TextBoxs names like "George" and "Mary" are not a good way to do things and making scripts that will loop through them get's much more complicated

This script does not check each control for values. If you want that feature I could add it later.
And this script enters the values into Sheet(1)

Try this and see if it will work for you

This script assumes you have 10 Comboboxes and Ten Textboxes

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To 10
        Sheets(1).Cells(Cells(Rows.Count, "A").End(xlUp).Row + 1, 1).Value = Controls("ComboBox" & i).Value
        Sheets(1).Cells(Cells(Rows.Count, "B").End(xlUp).Row + 1, 2).Value = Controls("TextBox" & i).Value
    Next
Application.ScreenUpdating = True
End Sub