Form & Data Sheet???????

C

Chris Watson

Is it possible to use a form template on sheet 1 that will put data in a
data list on sheet 2. I want to be able use the same form again and it
will transfer the data onto the list in the next row down, also when I
clear the form it will not erase the data on the first row. Ive tried
about everything and can't find a solution, I know I could use a user
form but I want to use a form template of my own design.
 
B

Bernie Deitrick

Chris,

Use code like this in a commandbutton click event:

Private Sub CommandButton1_Click()
Dim myCell As Range
Set myCell = Sheets("Sheet2").Range("A65536").End(xlUp)(2)
myCell(1, 1).Value = UserForm1.TextBox1.Text 'column A
myCell(1, 2).Value = UserForm1.TextBox2.Text 'column B
myCell(1, 3).Value = UserForm1.TextBox3.Text 'column C
myCell(1, 4).Value = UserForm1.TextBox4.Text 'column D
myCell(1, 5).Value = UserForm1.TextBox5.Text 'column E

End Sub

I hope you can see the pattern....

HTH,
Bernie
MS Excel MVP
 
C

Chris Watson

Hi Bernie, in "my cell" what do I put. Ive learn't most of the basics
about excel and VBA is something im just getting into, ive attached a
zip file of what im doing, can you give me a clue.


+-------------------------------------------------------------------+
|Filename: Test Sheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4415 |
+-------------------------------------------------------------------+
 
B

Bernie Deitrick

This populates myCell:

myCell(1, 1).Value = UserForm1.TextBox1.Text 'column A

I sued the myCell(1, 1) style to highlight the relationship between columns and the cell reference.

HTH,
Bernie
MS Excel MVP
 
C

Chris Watson

If cell D15 in form is start time how does mycell refer to this and
transfer to data summary, If I can get my head round this I could work
the rest out. I know a lot of people who use this forum just ask for
VBA code and copy and paste it into there work without realy
understanding it, I like to know how it works and why.
 
B

Bernie Deitrick

Chris,

I think I've misunderstood your original question. When you said that you
would design your own user form, I thought you meant to use a userform,
which is a specific Excel component, not worksheet designed as an input
sheet.

So, for example, you could use a macro like this, if your "user form" is a
worksheet named "User Form": assign the macro to a shape or button on the
sheet "User Form", and when it is clicked, it will transfer the data to a
second sheet, named "Data Sheeet", from specific cells.

Sub TransferData()
Dim myCell As Range
Dim myDSht As Worksheet
Dim myUFSht As Worksheet

Set myDSht = Sheets("Data Sheet")
Set myUFSht = Sheets("User Form")

Set myCell = myDSht.Range("A65536").End(xlUp)(2)
'First column of data
myCell(1, 1).Value = myUFSht.Range("D15").Value
'Second column of data
myCell(1, 2).Value = myUFSht.Range("E15").Value
'Third column of data
myCell(1, 3).Value = myUFSht.Range("G12").Value
'Fourth column of data
myCell(1, 4).Value = myUFSht.Range("H22").Value
'Fifth column of data
myCell(1, 5).Value = myUFSht.Range("Q3").Value

End Sub

Sorry for having been misleading.

HTH,
Bernie
MS Excel MVP


"Chris Watson" <[email protected]>
wrote in message
news:[email protected]...
 
C

Chris Watson

Thanks Bernie, As you can see with the attachment I posted earlier the
form has cells dotted all over the sheet dose the (1, 1) refer to a
paricula cell. Also is it possible to add more VBA to the button that
will clear the form to be re-used. Thanks Again Bernie
 
B

Bernie Deitrick

The (1,1) refers to the cells in the database that is being created. The
cells that are 'dotted all over' should be the cells in the second part of
the statements.

In this statement, the value from D15 (one of the "dotted all over" cells)
is put into the first column of the database.

myCell(1, 1).Value = myUFSht.Range("D15").Value

Bernie


"Chris Watson" <[email protected]>
wrote in message
news:[email protected]...
 
Top