r/excel•
8BallRunOut

Looping through controls in UserForm

solved

I'm trying to make a clearAll() function inside my UserForm which sets all the fields on the various MultiPages equal to "".

I have 4 fields on 7 pages of one MultiPage all named "cboCategory1", "cboArea2", etc...

So essentially, I'm wondering if I can turn these 28 lines of code into something more efficient.

​

Private Sub clearAllFields()
    Me.cboCategory1 = ""
    Me.cboArea1 = ""
    Me.txtDetails1 = ""
    Me.cboType1 = ""
    Me.cboCategory2 = ""
    Me.cboArea2 = ""
    Me.txtDetails2 = ""
    Me.cboType2 = ""
    Me.cboCategory3 = ""
    Me.cboArea3 = ""
    Me.txtDetails3 = ""
    Me.cboType3 = ""
    Me.cboCategory4 = ""
    Me.cboArea4 = ""
    Me.txtDetails4 = ""
    Me.cboType4 = ""
    Me.cboCategory5 = ""
    Me.cboArea5 = ""
    Me.txtDetails5 = ""
    Me.cboType5 = ""
    Me.cboCategory6 = ""
    Me.cboArea6 = ""
    Me.txtDetails6 = ""
    Me.cboType6 = ""
    Me.cboCategory7 = ""
    Me.cboArea7 = ""
    Me.txtDetails7 = ""
    Me.cboType7 = ""
End Sub

Posted Dec 23, 2019, 2:27 PM UTC · edited

35
fuzzius_navus616•

For sure.

You'll have to test for type to ensure you don't try to clear the labels

Sub CtrlLoop()
Dim ctl As Control

For each ctl in Me.Controls

' test type and clear control here
Next ctl
End Sub
3
8BallRunOutOP165•

Brilliant, I got by with this.

​

For Each ctl In Me.Controls
    If ctl.Name Like "cboCategory?" Or ctl.Name Like "cboArea?" Or ctl.Name Like "cboType?" Or ctl.Name Like "txtDetails?" Then
        ctl = ""
    End If
Next ctl

Thanks, Solution Verified!

2
Clippy_Office_AsstMOD•

You have awarded 1 point to fuzzius_navus

^I ^am ^a ^bot, ^please ^contact ^the ^mods ^for ^any ^questions.

1
fuzzius_navus616•

Since using Name, you could have probably shortened it to:

If Left(ctl.Name, 3) = "cbo" or Left(ctl.Name, 3) = "txt" Then
1
[deleted]•

[deleted]

1
Schuben37•

You should be able to modify this to suit your needs. I only tested it with a TextBox, but the other types should respond the same way by clearing the current value.

Sub TestClear()
    For Each pg In Me.MultiPage1.Pages
        For Each ctrl In pg.Controls
            If TypeOf ctrl Is MsForms.TextBox Or _
            TypeOf ctrl Is MsForms.ComboBox Or _
            TypeOf ctrl Is MsForms.ListBox Then
                ctrl.Value = ""
            End If
        Next ctrl
    Next pg
End Sub

This should, in theory, loop through every page on your multipage control and then if the control on that page is either a Textbox, Combobox or ListBox then it will set the value to "" for that control.

1