PDA

View Full Version : Capturing Data from a user form



loreign
04-15-2013, 03:56 PM
I took my first stab at VB today
I felt pretty good as I followed a youtube tutorial but to no avail

I check and checked and cannot see where I went wrong with the code.

Thoughts?

I created a form and need the info to complile in a master database

afzalw
04-15-2013, 06:30 PM
Put the code and give details of your problem.

loreign
04-16-2013, 08:53 AM
Private Sub CommandButton1_Click()
erow = Sheet1.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Text
Cells(erow, 2) = TextBox2.Text
Cells(erow, 3) = TextBox3.Text
Cells(erow, 4) = TextBox4.Text
Cells(erow, 5) = TextBox5.Text
Cells(erow, 6) = TextBox6.Text
Cells(erow, 7) = TextBox7.Text
Cells(erow, 8) = TextBox8.Text
Cells(erow, 9) = TextBox9.Text
Cells(erow, 10) = TextBox10.Text
Cells(erow, 11) = TextBox11.Text
Cells(erow, 12) = TextBox12.Text
Cells(erow, 13) = TextBox13.Text
Cells(erow, 14) = TextBox14.Text
Cells(erow, 15) = TextBox15.Text
Cells(erow, 16) = TextBox16.Text
Cells(erow, 17) = TextBox17.Text
Cells(erow, 18) = TextBox18.Text
Cells(erow, 19) = TextBox19.Text
Cells(erow, 20) = TextBox20.Text
Cells(erow, 21) = TextBox21.Text
Cells(erow, 22) = TextBox22.Text
Cells(erow, 23) = TextBox23.Text
Cells(erow, 24) = TextBox24.Text
Cells(erow, 25) = TextBox25.Text
Cells(erow, 26) = TextBox26.Text
End Sub

I have a userform with 26 questions and a commandbutton, I need these questions to populate into a sheet a2 thru z2 and then when someone else uses form and submits it pushes previous down and then populates that info

SamT
04-16-2013, 10:09 AM
Hello, Loreign, Welcome to VBAX.

In this line you are setting "erow" to the row following the last used row on sheet1
erow = Sheet1.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

when someone else uses form and submits it pushes previous down and then populates that info But it sounds like you want the new data to be inserted into row 1 and that you want to move all existing data down one row.

All sheets have two names in VBA, a CodeName and a "Tab" Name. In the VBA Project Explorer Pane, the "tab" Name is in Parentheses, and the CodeName precedes the "Tab" Name.

Contrariwise, In the VBA Properties Pane, The (Name) Property is the CodeName, and the Name Property is the "Tab" Name. More contrariness; in VBA code, the Codename is refered to without quotes. :dunno In Code, "Tab" Names are referred to as Sheet Collection indexes. IE, Sheets("Sheet1"). Finally, you can get the CodeName of a sheet using its CodeName Property. :bug:

x = Sheets("TabName").CodeName
What this means for your code above, is that you are referring to Sheet1 by its CodeName, and it won't matter if you change its "Tab" Name, you would still be referring to the same sheet.

Try this code
Private Sub CommandButton1_Click()
erow = Sheet1.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
With Sheet1
.Cells(erow, 1) = TextBox1.Text
.Cells(erow, 2) = TextBox2.Text
.Cells(erow, 3) = TextBox3.Text
'Etc
End With

Lonewolf
04-16-2013, 10:41 AM
Hello lorein i have tested the code above and it works fine it does updated the rows correctly going down without a problem.

make sure that you enter the name of the sheet correctly that could be the issue.

try to change it as the following:


eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ' where "Sheet1" is the name of the sheet where the data is enter


let me know if it worked for you.

mdmackillop
04-16-2013, 11:42 AM
Alternative coding for Post #3
Private Sub CommandButton1_Click()
erow = Sheet1.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
For i = 1 To 26
Cells(erow, i) = Controls("TextBox" & i).Text
Next
End Sub

loreign
04-17-2013, 08:57 AM
Thanks for the responses...I have to say I feel like such a dumbass :dunno

I have tried all the suggestions... since I have never done one of these I cannot even begin to understand what is not working...

I hit submit and the form just stays and does nothing...I tried attaching my file for you all to view... hope that worked :help

SamT
04-17-2013, 09:24 AM
:omg2:The :devil2: made me do it.

erow = Sheet1.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

That great big red 1 is supposed to be a lower case L


:rtfm: SAM!!!

loreign
04-17-2013, 09:39 AM
That was my worry posting here...
:banghead:

loreign
04-17-2013, 09:43 AM
mdmackillop..with your I got a run time error '1004' Application-defined error

only change I made was using commandbutton7_click() because that is what the button was called

SamT
04-17-2013, 10:02 AM
HE used a big red 1, too, ya know?

loreign
04-17-2013, 10:09 AM
yes sam, I figured I had it wrong all the way across...:bug:

SamT
04-17-2013, 10:25 AM
You don't have a TextBox11

Private Sub CommandButton7_Click()
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 1 To 26
If i < 11 Then
Cells(erow, i) = Controls("TextBox" & i).Text
Else
Cells(erow, i) = Controls("TextBox" & (i +1)).Text
End If
Next
End Sub

loreign
04-17-2013, 10:39 AM
Sam, you just might be my hero!:thumb

SamT
04-17-2013, 11:08 AM
The above is why I don't lik using default names for controls that are used to enter data. Instead I will; First name all the data columns; Then name the Data Entry controls exactly the same. That allows the use of code constructs like
NextRow = LastRow + 1
With Sheets("Sheet1")
For Each Ctl In Me.Controls
If TypeName(Ctl) = "TextBox" Then
'Because in a single column range, the Cell number = the row number
Range(Ctl.Name).Cells(NextRow).Value = Ctl.Value
End If
Next Ctl
End With
And I can add or delete controls and columns without worrying abut index numbers or column order.