I have created A userform in excel with the name add Add New Customer. The fileds in the form include as follows:
Customer ID
Name
Address
Date of Birth
Age
Sex
Comments
When I press the Tab button in the keyboard after writing the Value in Customer ID Row the tab moves to the OK button not the Address Row in the user form.
I want the form fields to be filled in the above manner when I press the tab.
I also want the age to appear in the age column or row of the form when I enter the Date of Birth in the form.
Here is the code Im using
Code: Select all
Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdClear_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
Next ctl
End Sub
Private Sub cmdOK_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("CustomerList")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Customer ID
If Trim(Me.txtCustomerID.Value) = "" Then
Me.txtCustomerID.SetFocus
MsgBox "Please enter a Customer ID"
Exit Sub
End If
If Not IsDate(Me.txtDateofBirth.Value) Then
MsgBox "The Date box must contain a date.", vbExclamation, "Add New Customer"
Me.txtDateofBirth.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCustomerID.Value
ws.Cells(iRow, 2).Value = Me.txtName.Value
ws.Cells(iRow, 3).Value = Me.txtAddress.Value
ws.Cells(iRow, 4).Value = Me.txtDateofBirth.Value
ws.Cells(iRow, 5).Value = Me.txtAge.Value
ws.Cells(iRow, 6).Value = Me.cboSex.Value
ws.Cells(iRow, 7).Value = Me.cboComments.Value
'clear the data
Me.txtCustomerID.Value = ""
Me.txtName.Value = ""
Me.txtAddress.Value = ""
Me.txtDateofBirth.Value = ""
Me.txtAge.Value = ""
Me.cboSex.Value = ""
Me.cboComments = ""
Me.txtCustomerID.SetFocus
End Sub
Private Sub Label6_Click()
End Sub
Private Sub txtCustomerID_Change()
End Sub
Private Sub UserForm_Initialize()
cboSex.List = Array("Male", "Female")
cboComments.List = Array("New", "Regular")
End Sub