Excel UserForm Data Entry (VBA) #7 - Clear (Reset) Controls
Learn how to clear all the controls in UserForm for the next entry in Excel VBA.
The code used in this video:
Private Sub btnCancel_Click()
Unload Me
End Sub
Private Sub btnOK_Click()
Dim ws As Worksheet
Set ws = Worksheets("Customers")
Dim newRow As Long
newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
ws.Cells(newRow, 2).Value = Me.txtSurname.Value
ws.Cells(newRow, 3).Value = Me.cbCountries.Value
If obMale.Value = True Then
ws.Cells(newRow, 4).Value = "Male"
Else
ws.Cells(newRow, 4).Value = "Female"
End If
If Me.cbPost.Value = True Then
ws.Cells(newRow, 5).Value = "Yes"
Else
ws.Cells(newRow, 5).Value = "No"
End If
If Me.cbTelephone.Value = True Then
ws.Cells(newRow, 6).Value = "Yes"
Else
ws.Cells(newRow, 6).Value = "No"
End If
For i = 0 To Me.lbDays.ListCount - 1
If Me.lbDays.Selected(i) Then
ws.Cells(newRow, 7).Value = ws.Cells(newRow, 7).Value + Me.lbDays.List(i) + " "
End If
Next i
Clear_Form
End Sub
Sub Clear_Form()
For Each ctrl In Me.Controls
Select Case TypeName(ctrl)
Case "TextBox"
ctrl.Text = ""
Case "ComboBox"
ctrl.ListIndex = -1
Case "OptionButton", "CheckBox"
ctrl.Value = False
Case "ListBox"
For i = 0 To ctrl.ListCount - 1
If ctrl.Selected(i) Then
ctrl.Selected(i) = False
End If
Next i
End Select
Next
End Sub
Private Sub UserForm_Initialize()
With cbCountries
.AddItem "Canada"
.AddItem "New Zealand"
End With
With Me.lbDays
.AddItem "Monday"
.AddItem "Tuesday"
.AddItem "Wednesday"
End With
End Sub
And that one below goes into Module1:
Sub Button1_Click()
ufCustomers.Show
End Sub
Watch video Excel UserForm Data Entry (VBA) #7 - Clear (Reset) Controls online without registration, duration hours minute second in high quality. This video was added by user InAnOffice 06 February 2014, don't forget to share it with your friends and acquaintances, it has been viewed on our site 34,133 once and liked it 90 people.