Clearing 'Groups' Of Userform Controls (avoiding clearing all userform controls, or doing each individually)

[Ark68]

In my userform, I have 10 frames each with a variety of controls (textboxes, comboboxes, check boxes) in each specific to that frame. Some frames have as little as 4 controls, others as many as 15.

Is there an efficient way that I can clear the values of each of these controls in just the frames (not the entire form) rather than having to code each control's property individually? Ihave this pseudo code below, but I suspect I can't simply isolate groups of controls based on their unique frame.

Pseudo code:

With Me
    For frm = 1 to 10
        nameFrm = ? frame name of frm(i) ?   'eg frm2d_setup_exp10
        for each control in nameFrm 'loop through each of the 12 controls in frm2d_setup_exp10
            if control is a textbox value = false
            if control is a combobox or textbox value = ""
        next
    Next frm
End With

 

[Ark68]

Note:
Frame names all share the prefix "frm2" with each having a unique letter (a-j) affixed. eg frm2a - frm2j

 

[Domenic]

Try . . .

Private Sub CommandButton1_Click()

    Dim i As Long
    Dim frm As MSForms.Frame
    Dim ctrl As MSForms.Control
    Dim arr As Variant
    
    arr = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j")
    
    For i = LBound(arr) To UBound(arr)
        Set frm = Me.Controls("frm2" & arr(i) & "_setup_exp10") 'eg frm2d_setup_exp10
        For Each ctrl In frm.Controls
            Select Case TypeName(ctrl)
                Case "TextBox", "ComboBox"
                    ctrl.Value = ""
                Case "CheckBox"
                    ctrl.Value = False
            End Select
        Next ctrl
    Next i
    
End Sub

Hope this helps!

 

[mikerickson]

ClearOneFrame gives good control. ClearAllFrames uses that.

Sub ClearAllFrames()
    Dim aControl As MSForms.Control
    For Each aControl In Me.Controls
        If TypeName(aControl) = "Frame" Then
            Call ClearOneFrame(aControl)
        End If
    Next aControl
End Sub

Sub ClearOneFrame(aFrame As MSForms.Frame)
    Dim OneControl As MSForms.Control, i As Long
  
    For Each OneControl In aFrame.Controls
        With OneControl
        Select Case TypeName(OneControl)
            Case "ListBox", "ComboBox"
                    For i = 0 To .ListCount - 1
                        .Selected(i) = False
                    Next i
              
            Case "TextBox"
                .Value = vbNullString
              
            Case "OptionButton", "Checkbox", "ToggleButton"
                .Value = False
              
            Case "TabStrip", "MultiPage"
                .Value = 0
              
            Case "Frame"
                Call ClearOneFrame(OneControl)
          
        End Select
        End With
    Next OneControl
End Sub

 

[dmt32]

Hi,
try

   Dim ctrl    As Control
    Dim i       As Long
   
    For i = 97 To 106
        For Each ctrl In Me.Controls("frm2" & Chr(i)).Controls
       
            ctrl.Value = IIf(VarType(ctrl) = vbBoolean, False, "")
       
        Next ctrl
    Next i

Dave

 

[dmt32]

whoops, omitted the bit at the end of the name

    Dim ctrl    As Control
    Dim i       As Long
   
    For i = 97 To 106
        For Each ctrl In Me.Controls("frm2" & Chr(i) & "_setup_exp10").Controls
       
            ctrl.Value = IIf(VarType(ctrl) = vbBoolean, False, "")
       
        Next ctrl
    Next i

 

[Ark68]

Thank you all for your help. I really appreciate the different solutions that are available. I chose to test mikerickson's solution as it didn't rely on the full frame name. My original post was misleading. All 10 frames have a unique name, sharinng only the prefix f2. The letters are different, and in my example "_setup_exp10" are unique among the other frame names.

Mikerickson, I am getting an error in the ClearAllFrames procedure. "Invalid use of Me keyword".
I am calling this procedure from within my userform initialization code and the form hasn't been opened yet. Is this the cause of this error? If so, how would I overcome it?

 

[dmt32]

My original post was misleading. All 10 frames have a unique name, sharinng only the prefix f2. The letters are different, and in my example "_setup_exp10" are unique among the other frame names.

No worries & glad found solution - always helpful to forum if you fully share requirement.

Dave

 

[Domenic]

If your procedure is located in a regular module, you won't be able to refer to your userform using the Me keyword. You'll need to refer to it by name, for example . . .

For Each aControl In UserForm1.Controls

Any reason, though, why the code isn't located within the userform code module?

 

[Ark68]

Hi Domenic, thank you for pointing that out. I put the two procedures back into the userform code module and things are working wonderfully.