VBA UserForm Data Entry Automation
Activating UserForm1
This section initializes the UserForm1 by populating the ComboBox1 with city names.
Private Sub UserForm_Activate()
ComboBox1.AddItem "Rancaguita"
ComboBox1.AddItem "Santiaguitito"
ComboBox1.AddItem "Viñitita"
ComboBox1.AddItem "Concepcioncito"
ComboBox1.AddItem "Chaitencitito"
ComboBox1.AddItem "La Serena"
ComboBox1.Text = "Select an Option"
End Sub
Navigating Controls
This code moves the cursor to the next control after selecting an item in ComboBox1.
Private Sub ComboBox1_Click()
ComboBox1.Enabled = False
TextBox4.Enabled = True
TextBox4.SetFocus
End Sub
Validating Name Input
This section validates the Name input in TextBox1, ensuring only numeric values are entered.
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
If Not IsNumeric(TextBox1.Text) And (TextBox1.Text) "" Then
Dim HH As String
HH = MsgBox("OK, proceed to the next control", vbInformation, "OK")
TextBox1.Enabled = False
TextBox2.Enabled = True
TextBox2.SetFocus
Else
Dim RH As String
RH = MsgBox("Error, enter only numbers", vbCritical, "Warning")
TextBox1.Text = ""
TextBox1.SetFocus
End If
End If
End Sub
Validating Address Input
This section validates the Address input in TextBox2, ensuring only numeric values are entered.
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
If Not IsNumeric(TextBox2.Text) Then
Dim HH As String
HH = MsgBox("OK, proceed to the next control", vbInformation, "OK")
TextBox2.Enabled = False
TextBox3.Enabled = True
TextBox3.SetFocus
Else
Dim RH As String
RH = MsgBox("Error, enter only numbers", vbCritical, "Warning")
TextBox2.Text = ""
TextBox2.SetFocus
End If
End If
End Sub
Validating Phone Input
This section validates the Phone input in TextBox3, ensuring only numeric values are entered.
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
If IsNumeric(TextBox3.Text) Then
Dim HH As String
HH = MsgBox("OK, proceed to the next control", vbInformation, "OK")
TextBox3.Enabled = False
ComboBox1.Enabled = True
ComboBox1.SetFocus
Else
Dim RH As String
RH = MsgBox("Error, Enter only numbers", vbCritical, "Warning")
TextBox3.Text = ""
TextBox3.SetFocus
End If
End If
End Sub
Validating Age Input
This section validates the Age input in TextBox4, ensuring only numeric values are entered.
Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
If IsNumeric(TextBox4.Text) Then
Dim HH As String
HH = MsgBox("OK, pass the following", vbInformation, "OK")
TextBox4.Enabled = False
CommandButton1.Enabled = True
CommandButton1.SetFocus
Else
Dim RH As String
RH = MsgBox("ERROR, Enter only numbers", vbCritical, "Warning")
TextBox4.Text = ""
TextBox4.SetFocus
End If
End If
End Sub
Recording Data
This section records the data entered in the UserForm to an Excel sheet.
Private Sub CommandButton1_Click()
Range("B4").Value = "Name"
Range("C4").Value = "Address"
Range("D4").Value = "Phone"
Range("E4").Value = "City"
Range("F4").Value = "Age"
Range("B4:F20").Borders.Value = 9
Range("B4:F20").Font.Bold = True
Range("B4:F4").Font.Size = 12
Range("B4:F4").Interior.Color = vbBlue
Range("B4:F4").Font.Color = vbWhite
Range("B4").ColumnWidth = 18
Range("C4").ColumnWidth = 18
Range("D4").ColumnWidth = 7
Range("E4").ColumnWidth = 15
Range("F4").ColumnWidth = 7
Range("B5:F5").Font.Size = 10
Range("B5:F20").Font.Color = vbBRed
Range("B5:F20").Interior.Color = vbYellow
Range("B5").Select
Selection.EntireRow.Insert
Sheets("Sheet1").Range("B5").Value = TextBox1
Dim RR As String
RR = MsgBox("Data Recorded", vbInformation, "SAVE")
CommandButton1.Enabled = False
CommandButton2.Enabled = True
CommandButton3.Enabled = True
‘Enable the Quit button
CommandButton2.SetFocus’ Leave the cursor in Other Income
End Sub
_____________________________________________________________________________
‘Button Programming Other Income
Private Sub CommandButton2_Click ()
TextBox1.Text = “” ‘Clear the contents of TextBox1 (Name)
TextBox2.Text = “” ‘Clear the contents of TextBox2 (Address)
TextBox3.Text = “” ‘Clear the contents of TextBox3 (Phone)
TextBox4.Text = “” ‘Clear the contents of TextBox4 (Age)
ComboBox1.Text = “” ‘Clear the ComboBox1 (City)
CommandButton2.Enabled = False ‘Disable the same
TextBox1.Enabled = True ‘Enable TextBox1 (name) before it reaches your cursor
TextBox1.SetFocus’ leaves the cursor in the Textbox1 (Name)
End Sub
‘Exit Button Programming
Private Sub CommandButton3_Click ()
Dim AA As String
As String Dim HH
AA = “John Doe”
HH = MsgBox ( “This insurance Exit”, vbYesNo + vbQuestion, “little questions”)
If HH = vbYes Then
AB As String Dim
AB = MsgBox ( “OK, Chait, the author is:” & AA, vbInformation, “See You”)
End
Else
As String Dim AX
AX = MsgBox ( “Return Program”, vbExclamation, “Return”)
End If
End Sub