Input Form on Worksheet 1, Data on Worksheet 2

[Jim in Spokane]

I created an input text boxes to collect name and social
security number on worksheet1. Then click on a command
button to transfer the name and SSN to worksheet2 and
then leave worksheet 1 blank for the next person to sign
up.

I want to use the single input form on worksheet 1 to
build a database on worksheet 2.

Can I do this?

Thanks
Jim

 

[John Williams]

I created an input text boxes to collect name and social
security number on worksheet1. Then click on a command
button to transfer the name and SSN to worksheet2 and
then leave worksheet 1 blank for the next person to sign
up.

I want to use the single input form on worksheet 1 to
build a database on worksheet 2.

Can I do this?

Thanks
Jim

I've implemented this using a VBA UserForm. It contains 2 labels, 2
textboxes (txtName and txtSSN) and a command button. The code inside
UserForm1 is:

Private Sub CommandButton1_Click()
Dim dataSheet As Variant
Dim nextRow As Integer

'Create the Database sheet with column headings if it doesn't
exist

If Not worksheetExists("Database", ThisWorkbook) Then
Set dataSheet = Sheets.Add
dataSheet.Name = "Database"
Range("A1").Value = "Name"
Range("B1").Value = "SSN"
End If

Sheets("Database").Activate

'Determine the next free row and add the name and SSN entries

nextRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row + 1
ActiveCell.Cells(nextRow, 1).Value = txtName.Text
ActiveCell.Cells(nextRow, 2).Value = txtSSN.Text

'Blank out the fields on the input form and return to Sheet1

txtName.Text = ""
txtSSN.Text = ""
Sheets("Sheet1").Activate

End Sub

Function worksheetExists(WSName As String, Optional WB As Workbook) As
Boolean
On Error Resume Next
worksheetExists = CBool(Len(IIf(WB Is Nothing, ActiveWorkbook,
WB).Worksheets(WSName).Name))
End Function

-----

The form is automatically displayed when the workbook is opened by
defining the following subroutine in the ThisWorkbook workbook within
VBA.

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
Load UserForm1
UserForm1.Show
End Sub