Running a Userform from Excel
 

My code:

Private Sub Frame1_Click()

End Sub

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub cmdCreate_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Prospects")

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

'check for a Company name
If Trim(Me.txtCompany.Value) = "" Then
Me.txtCompany.SetFocus
MsgBox "Please enter a valid Company"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtContact.Value
ws.Cells(iRow, 2).Value = Me.txtCompany.Value
ws.Cells(iRow, 3).Value = Me.txtStreet.Value
ws.Cells(iRow, 4).Value = Me.txtDistrict.Value
ws.Cells(iRow, 5).Value = Me.txtTownCity.Value
ws.Cells(iRow, 6).Value = Me.txtStateProvince.Value
ws.Cells(iRow, 7).Value = Me.txtPostCode.Value
ws.Cells(iRow, 8).Value = Me.txtCountry.Value
ws.Cells(iRow, 9).Value = Me.txtTelephone.Value
ws.Cells(iRow, 10).Value = Me.txtFax.Value
ws.Cells(iRow, 11).Value = Me.txtEmail.Value
ws.Cells(iRow, 12).Value = Me.txtWeb.Value
ws.Cells(iRow, 13).Value = Me.txtFirstContacted.Value
ws.Cells(iRow, 14).Value = Me.txtLastContacted.Value
ws.Cells(iRow, 15).Value = Me.txtComments.Value

'clear the data
Me.txtContact.Value = ""
Me.txtCompany.Value = ""
Me.txtStreet.Value = ""
Me.txtDistrict.Value = ""
Me.txtTownCity.Value = ""
Me.txtStateProvince.Value = ""
Me.txtPostCode.Value = ""
Me.txtCountry.Value = ""
Me.txtTelephone.Value = ""
Me.txtFax.Value = ""
Me.txtEmail.Value = ""
Me.txtWeb.Value = ""
Me.txtFirstContacted.Value = ""
Me.txtLastContacted.Value = ""
Me.txtComments.Value = ""
Me.txtCompany.SetFocus

End Sub

Private Sub cmdPrintCreate_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Prospects")

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

'check for a Company name
If Trim(Me.txtCompany.Value) = "" Then
Me.txtCompany.SetFocus
MsgBox "Please enter a valid Company"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtContact.Value
ws.Cells(iRow, 2).Value = Me.txtCompany.Value
ws.Cells(iRow, 3).Value = Me.txtStreet.Value
ws.Cells(iRow, 4).Value = Me.txtDistrict.Value
ws.Cells(iRow, 5).Value = Me.txtTownCity.Value
ws.Cells(iRow, 6).Value = Me.txtStateProvince.Value
ws.Cells(iRow, 7).Value = Me.txtPostCode.Value
ws.Cells(iRow, 8).Value = Me.txtCountry.Value
ws.Cells(iRow, 9).Value = Me.txtTelephone.Value
ws.Cells(iRow, 10).Value = Me.txtFax.Value
ws.Cells(iRow, 11).Value = Me.txtEmail.Value
ws.Cells(iRow, 12).Value = Me.txtWeb.Value
ws.Cells(iRow, 13).Value = Me.txtFirstContacted.Value
ws.Cells(iRow, 14).Value = Me.txtLastContacted.Value
ws.Cells(iRow, 15).Value = Me.txtComments.Value

'This is the print command
Me.printform

'clear the data
Me.txtContact.Value = ""
Me.txtCompany.Value = ""
Me.txtStreet.Value = ""
Me.txtDistrict.Value = ""
Me.txtTownCity.Value = ""
Me.txtStateProvince.Value = ""
Me.txtPostCode.Value = ""
Me.txtCountry.Value = ""
Me.txtTelephone.Value = ""
Me.txtFax.Value = ""
Me.txtEmail.Value = ""
Me.txtWeb.Value = ""
Me.txtFirstContacted.Value = ""
Me.txtLastContacted.Value = ""
Me.txtComments.Value = ""
Me.txtCompany.SetFocus

End Sub

Private Sub frmProspects_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub