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
Смотрите видео Excel UserForm Data Entry (VBA) #7 - Clear (Reset) Controls онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь InAnOffice 06 Февраль 2014, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 34,133 раз и оно понравилось 90 людям.