Patriot2879
Well-known Member
- Joined
- Feb 1, 2018
- Messages
- 1,259
- Office Version
- 2010
- Platform
- Windows
Hi all, good morning i have made the code below which works well but it doesn't clear the combiboxes. can you advise please.
'---------------------------------------------------------------------------------------
' DateTime : 09/05/2007 08:43
' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)
' Website : click here for more examples and Excel Consulting
' Purpose : Loops through controls of UserForm & clears setting
'
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
' projects but please leave this header intact.
'---------------------------------------------------------------------------------------
Option Explicit
Public Function ClearAll(frm As MSForms.UserForm) As Boolean
' clear out all controls
Dim Octrl As Control
' if any error occurs, just exit
On Error GoTo ExitFunc
' loop through controls, figure out type and
' use appropriate method to clear it
For Each Octrl In frm.Controls
Select Case TypeName(Octrl)
Case "TextBox": Octrl = Empty
Case "CheckBox", "OptionButton": Octrl.Value = False
Case "ComboBox", "ListBox": Octrl.ListIndex = -1
Case Else:
End Select
Next Octrl
ExitFunc:
Set Octrl = Nothing
End Function
'---------------------------------------------------------------------------------------
' DateTime : 09/05/2007 08:43
' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)
' Website : click here for more examples and Excel Consulting
' Purpose : Loops through controls of UserForm & clears setting
'
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
' projects but please leave this header intact.
'---------------------------------------------------------------------------------------
Option Explicit
Public Function ClearAll(frm As MSForms.UserForm) As Boolean
' clear out all controls
Dim Octrl As Control
' if any error occurs, just exit
On Error GoTo ExitFunc
' loop through controls, figure out type and
' use appropriate method to clear it
For Each Octrl In frm.Controls
Select Case TypeName(Octrl)
Case "TextBox": Octrl = Empty
Case "CheckBox", "OptionButton": Octrl.Value = False
Case "ComboBox", "ListBox": Octrl.ListIndex = -1
Case Else:
End Select
Next Octrl
ExitFunc:
Set Octrl = Nothing
End Function
VBA Code:
Private Sub CommandButton2_Click()
ClearAll Me
End Sub