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

If obMale.Value = True Then
ws.Cells(newRow, 4).Value = "Male"
Else
ws.Cells(newRow, 4).Value = "Female"
End If

ws.Cells(newRow, 5).Value = "Yes"
Else
ws.Cells(newRow, 5).Value = "No"
End If

ws.Cells(newRow, 6).Value = "Yes"
Else
ws.Cells(newRow, 6).Value = "No"
End If

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
.AddItem "Monday"
.AddItem "Tuesday"
.AddItem "Wednesday"
End With

End Sub
And that one below goes into Module1:
Sub Button1_Click()
ufCustomers.Show
End Sub

[MB-lemk]

Thanks for the help! I couldn't clear the password inside the UserForm after login. The reason was that I was not accessing ComboBox properties.

[danielcarlyle]

Hi, This was very helpful: Thank you. Follow-up question: How does one clear all refedit controls in a userform? Your code doesn't seem to cover refedit controls.

[the_feature_selector]

thank you, i couldnt find combo box fix anywhere

[Augustin]

cool, but how do you create the button for (Add customer) that runs the code for the form?

[tylerademy]

Hi I want to do this for regular ActiveX controls embedded in the worksheet, not on a userform. How can I deselect or reset all controls from a worksheet that is not on a userform? Is it possible?

[carlosirizarry]

Hello, I am getting a error variable not defined and it highlighted the ctrl code. Can you assist?

[Hamid_Gassali]

thank you very much.,  please add buttons on this same user form which that search and update data  

[owls]

The days of the week did not clear in your example.