VBA UserForm: Data Entry and Validation
VBA UserForm: Data Entry and Validation
Form Activation
In the UserForm’s Activate event, comboboxes are populated:
Private Sub UserForm_Activate() 'Filling the Civil Status ComboBox1 ComboBox1.AddItem "Married" ComboBox1.AddItem "Separated" ComboBox1.AddItem "Divorced" 'Filling the City ComboBox2 ComboBox2.AddItem "Rancaguita" ComboBox2.AddItem "Santiago" ComboBox2.AddItem "Concepción" ComboBox2.AddItem "La Serena" ComboBox2.AddItem "Viña del Mar" 'Filling the AFP ComboBox3 ComboBox3.AddItem "Provida" ComboBox3.AddItem "Cuprum" ComboBox3.AddItem "Habitat" ComboBox3.AddItem "ING" ComboBox3.AddItem "FUSAT" End Sub Name Input
The following code validates the Name textbox input:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then 'Enter key pressed If Not IsNumeric(TextBox1.Text) And TextBox1.Text <> "" Then MsgBox "Enter OK, go to the next", vbInformation, "OK" TextBox1.Enabled = False TextBox2.Enabled = True TextBox2.SetFocus Else MsgBox "Error: Cannot enter numbers only", vbCritical, "ERROR" TextBox1.Text = "" TextBox1.SetFocus End If End IfEnd SubAddress Input
Address input validation:
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then 'Enter key pressed If Not IsNumeric(TextBox2.Text) And TextBox2.Text <> "" Then MsgBox "Enter OK, go to the next", vbInformation, "OK" TextBox2.Enabled = False TextBox3.Enabled = True TextBox3.SetFocus Else MsgBox "Error: Cannot enter numbers only", vbCritical, "ERROR" TextBox2.Text = "" TextBox2.SetFocus End If End IfEnd SubPhone Input
Phone number validation:
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then 'Enter key pressed If IsNumeric(TextBox3.Text) Then MsgBox "Enter OK, go to the next", vbInformation, "OK" TextBox3.Enabled = False ComboBox1.Enabled = True ComboBox1.SetFocus Else MsgBox "Error: Enter numbers only", vbCritical, "ERROR" TextBox3.Text = "" TextBox3.SetFocus End If End IfEnd SubCivil Status Selection
Moves cursor to the next control when an item is selected:
Private Sub ComboBox1_Click() ComboBox1.Enabled = False ComboBox2.Enabled = True ComboBox2.SetFocusEnd SubCity Selection
Similar to Civil Status:
Private Sub ComboBox2_Click() ComboBox2.Enabled = False ComboBox3.Enabled = True ComboBox3.SetFocusEnd SubAFP Selection
Moves cursor to the next control when an item is selected:
Private Sub ComboBox3_Click() ComboBox3.Enabled = False TextBox4.Enabled = True TextBox4.SetFocusEnd SubAge Input
Age input validation:
Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then 'Enter key pressed If IsNumeric(TextBox4.Text) Then MsgBox "Enter OK, go to the next", vbInformation, "OK" TextBox4.Enabled = False TextBox5.Enabled = True TextBox5.SetFocus Else MsgBox "Error: Enter numbers only", vbCritical, "ERROR" TextBox4.Text = "" TextBox4.SetFocus End If End IfEnd SubBase Salary Input
Base salary validation (must be between 100,000 and 999,999):
Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then 'Enter key pressed If IsNumeric(TextBox5.Text) Then If TextBox5.Text >= 100000 And TextBox5.Text <= 999999 Then If TextBox5.Text > 200000 Then 'Bonus calculation MsgBox "Enter OK, go to the next", vbInformation, "OK" TextBox5.Enabled = False TextBox6.Text = 50000 TextBox7.Text = (Val(TextBox5.Text) * 0.2) TextBox8.Text = 30000 TextBox9.Enabled = True TextBox9.SetFocus Else MsgBox "Entry OK, go to the next", vbInformation, "OK" TextBox5.Enabled = False TextBox6.Text = 50000 TextBox7.Text = (Val(TextBox5.Text) * 0.2) TextBox8.Text = 50000 TextBox9.Enabled = True TextBox9.SetFocus End If Else MsgBox "Error: Base salary out of range", vbCritical, "ERROR" TextBox5.Text = "" TextBox5.SetFocus End If Else MsgBox "Error: Enter numbers only", vbCritical, "ERROR" TextBox5.Text = "" TextBox5.SetFocus End If End IfEnd SubAdvance Input
Advance input validation (cannot exceed 50% of base salary):
Private Sub TextBox9_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then 'Enter key pressed If IsNumeric(TextBox9.Text) Then If TextBox9.Text <= (Val(TextBox5.Text) * 0.5) Then MsgBox "Entry OK, Calculate Salary Liquido", vbInformation, "OK" TextBox10.Text = (Val(TextBox5.Text) + Val(TextBox6.Text) + Val(TextBox7.Text) + Val(TextBox8.Text)) - (Val(TextBox9.Text)) TextBox9.Enabled = False CommandButton1.Enabled = True CommandButton1.SetFocus Else MsgBox "Error: Advance out of range", vbCritical, "ERROR" TextBox9.Text = "" TextBox9.SetFocus End If Else MsgBox "Error: Enter numbers only", vbCritical, "ERROR" TextBox9.Text = "" TextBox9.SetFocus End If End IfEnd Sub