Info from userform to spreadsheet.

[CrankyLemming]

Hi

How can I get the information input into the textboxes of a userform
to appear in the relevant cells on a spreadsheet in exactly the same
way that they do when using Data -> Form on the toolbar, ie the eight
textboxes fill A1:H1 when a button is clicked on the form? Then, the
next time the form is used it fills A2:H2, then A3:H3, etc.

I know I can use the Data-> Form method if I create column titles, but
I want to use the form I designed.
*stamps foot*

Any help would be greatly appreciated.

TIA

Steve

 

[Earl Kiosterud]

Steve,

You have to write a macro to respond to your text boxes and put the data
into your table. The controls in a userform are ActiveX controls, and have
defined event procedures that run automatically when stuff happens to them
(click, mouse over, double-click, etc.). Try double-clicking a text box.
It will take you to the code module for the UserForm, and put in such a sub:

Private Sub TextBox2_Change()

End Sub

You put your code within this stub.

 

[Paul B]

Steve, here is one way

Private Sub CommandButton1_Click()
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = UserForm1.TextBox1.Value
ActiveCell.Offset(0, 1).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(0, 2).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(0, 3).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(0, 4).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(0, 5).Value = UserForm1.TextBox6.Value
ActiveCell.Offset(0, 6).Value = UserForm1.TextBox7.Value
ActiveCell.Offset(0, 7).Value = UserForm1.TextBox8.Value
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.TextBox6.Value = ""
UserForm1.TextBox7.Value = ""
UserForm1.TextBox8.Value = ""
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[CrankyLemming]

Steve, here is one way

Private Sub CommandButton1_Click()
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = UserForm1.TextBox1.Value
ActiveCell.Offset(0, 1).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(0, 2).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(0, 3).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(0, 4).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(0, 5).Value = UserForm1.TextBox6.Value
ActiveCell.Offset(0, 6).Value = UserForm1.TextBox7.Value
ActiveCell.Offset(0, 7).Value = UserForm1.TextBox8.Value
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.TextBox6.Value = ""
UserForm1.TextBox7.Value = ""
UserForm1.TextBox8.Value = ""
End Sub

Paul,this works great for transfering the information, but subsequent
form use overwrites what has previously been moved over, ie, always
writes to the same cells, not their equivalent on the next row down.

I've made a couple of minor changes to your code, but don't think they
affect the overall result:

ActiveCell.Offset(1, 0).Select is now (3, 1) to start in cell B4,
and I've reduced the form to 7 items, but have removed the unnecessary
line from the code.

I assume I need to define a range for the table, ie B4:H22, but then
draw a blank.

Steve

 

[Paul B]

Steve,
"fill A1:H1 when a button is clicked on the form? Then, the
next time the form is used it fills A2:H2, then A3:H3, etc."

If you want to put the info in column B instead of A, and assuming that you
already have data in B3, the first part of the code looks for the next empty
cell in Column B and puts the data there, so each time it runs the data will
be put in the next row down

Private Sub CommandButton1_Click()
Range("B65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = UserForm1.TextBox1.Value
ActiveCell.Offset(0, 1).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(0, 2).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(0, 3).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(0, 4).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(0, 5).Value = UserForm1.TextBox6.Value
ActiveCell.Offset(0, 6).Value = UserForm1.TextBox7.Value
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.TextBox6.Value = ""
UserForm1.TextBox7.Value = ""
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[CrankyLemming]

Paul B wrote

If you want to put the info in column B instead of A, and assuming that you
already have data in B3, the first part of the code looks for the next empty
cell in Column B and puts the data there, so each time it runs the data will
be put in the next row down

Ah. Damn.

It was my messing around with it that stopped it working properly. Got
it cleared up now and it works perfectly. Thanks a lot for your help.

Steve