How to reset only some controls on UserForm?

[keatlim]

Dear all,

I am new to Excel VBA.
Been trying to deal with the following problem for days but could not figure out a solution.

I want to enter data for individuals.

• Each individual has data that are "static" e.g. ID, age, gender and data that "varies" e.g. addresses and contact numbers
• I would like to enter the data in long format, such that the "static" data will be repeated for every row of varying data i.e. address and contact numbers
• To avoid re-entering the "static" data for every individual, I would like to write codes that remove only the "varying data" before I enter the next "varying" data.
• In other words, I want to remove address 1 and contact detail 1 before I enter address 2 and contact detail 2, and to remove address 2 and contact detail 2 before I enter address 3 and contact detail 3 for the same individual ID, age and gender.

Below is my current code:

• Controls 1 to 10 are for "static" data whereas Controls 11 to 20 are for "varying" data.
• The codes are intended to remove only Controls 11 to 20 which could be textbox, combobox, option button or listbox.
• These codes give "Run-time error 438. Object doesn't support this property or method".
• When I click the "Debug" button, it highlights "For Each ctrl In Me.Controls(x)" but I am not sure how else to edit it.

Can anyone help?
Any advice would be appreciated.

Thank you very much in advanced!

Private Sub Clear_Prog()

Dim x As Integer
For x = 11 To 20
    For Each ctrl In Me.Controls(x)
        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
Next x

End Sub

 

[keatlim]

This loops through all the controls on the second page (page(1)) of a Multipage object.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Clear_Ind_Var()
    
    [COLOR=darkblue]Dim[/COLOR] ctrl [COLOR=darkblue]As[/COLOR] MSForms.Control
    
[B]    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ctrl [COLOR=darkblue]In[/COLOR] Me.MultiPage1.Pages(1).Controls[/B]
        [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] TypeName(ctrl)
            [COLOR=darkblue]Case[/COLOR] "TextBox"
                ctrl.Text = ""
            [COLOR=darkblue]Case[/COLOR] "ComboBox"
                ctrl.ListIndex = -1
            [COLOR=darkblue]Case[/COLOR] "OptionButton", "CheckBox"
                ctrl.Value = [COLOR=darkblue]False[/COLOR]
            [COLOR=darkblue]Case[/COLOR] "ListBox"
                For i = 0 [COLOR=darkblue]To[/COLOR] ctrl.ListCount - 1
                    [COLOR=darkblue]If[/COLOR] ctrl.Selected(i) [COLOR=darkblue]Then[/COLOR]
                        ctrl.Selected(i) = [COLOR=darkblue]False[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]Next[/COLOR] i
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    [COLOR=darkblue]Next[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Thank you very much AlphaFrog!
This works perfectly!
Much appreciated!

I actually have more than a page for the individual varying details.
So I adapted your codes:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Clear_Ind_Var()

Dim x As Integer
Dim ctrl As MSForms.Control

'Loop through all controls from Page 3 to 6
For x = 2 To 5
    For Each ctrl In Me.MultiPage1.Pages(x).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
Next x

End Sub

 

[AlphaFrog]

You're welcome. Thanks for the feedback.