CommandButton userform vs worksheet

[mike31z]

I have a Userform with 15 text box that I entered participant data into and when finished I click on the Command Button to post the data to the database on a seperate worksheet. That all works fine but now is the time to evolve our spreadsheet.

I want to be able to do the same thing from a worksheet and bypass the userform. I want to enter the data into worksheet and have couple of cell that have a VLookup reeference and then post the cell values to the registration data base.

Below is my userform command button private Sub.
Can this code be modified to change the location of Me.txtAta to a worksheet cell reference Invoice("A2")???????

ws.Cells(iRow, 1).Value = Me.txtAta.Value


Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Reg")
'This is to print the form I hope
'mike p 4Feb09
Me.PrintForm

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a ATA number
If Trim(Me.txtAta.Value) = "" Then
Me.txtAta.SetFocus
MsgBox "Please enter a ATA number"
Exit Sub
End If

'copy the data to the database

ws.Cells(iRow, 3).Value = Me.txtName.Value
ws.Cells(iRow, 4).Value = Me.txtSsq.Value
ws.Cells(iRow, 5).Value = Me.txtOp16.Value
ws.Cells(iRow, 6).Value = Me.txtCl16.Value
ws.Cells(iRow, 7).Value = Me.txtLady.Value
ws.Cells(iRow, 8).Value = Me.txtSpcl.Value
ws.Cells(iRow, 9).Value = Me.txtDs.Value
ws.Cells(iRow, 10).Value = Me.txtDc.Value
ws.Cells(iRow, 11).Value = Me.txtHs.Value
ws.Cells(iRow, 12).Value = Me.txtHo.Value
ws.Cells(iRow, 13).Value = Me.txtHy.Value
ws.Cells(iRow, 14).Value = Me.txtH50.Value

'clear the data
Me.txtAta.Value = ""
Me.txtName.Value = ""
Me.txtSsq.Value = ""
Me.txtOp16.Value = ""
Me.txtCl16.Value = ""
Me.txtLady.Value = ""
Me.txtSpcl.Value = ""
Me.txtDs.Value = ""
Me.txtDc.Value = ""
Me.txtHs.Value = ""
Me.txtHo.Value = ""
Me.txtHy.Value = ""
Me.txtH50.Value = ""
Me.txtAta.SetFocus



Mike in Wisconsin

 

[RoryA]

If Invoice is the sheet with the button on it, then just:

Me.Range("A2").Value

would work instead of Me.txtAta if you are using a button from the Control toolbox.

 

[mike31z]

Thanks for the quick reply let me try to make it work.

 

[mike31z]

Solved. Here the code it is easy to adapt to more cell to copy and past.

Thanks for the help

Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Object
Dim response As String

Set LastRow = Sheets("Reg").Range("a65536").End(xlUp)
'Set LastRow = Sheets("Reg").Range("A" & Rows.Count).End(xlUp)

LastRow.Offset(1, 0).Value = Sheets("Invoice").Range("F7").Value
LastRow.Offset(1, 2).Value = Sheets("Invoice").Range("F8").Value
LastRow.Offset(1, 3).Value = Sheets("Invoice").Range("F10").Value
LastRow.Offset(1, 4).Value = Sheets("Invoice").Range("F11").Value

response = MsgBox("Do you want to clear the form?", _
vbYesNo)

If response = vbYes Then
Me.Range("F7").Value = ""
Me.Range("F8").Value = ""
Me.Range("F10").Value = ""
Me.Range("F11").Value = ""
Me.Range("F7").Select
End If
End Sub