Change the value/content of several UserForm-controls using VBA in Microsoft Excel

In an Excel 5/95 dialogsheet it is possible to change the value/content of a collection of controls by
looping through the controls in the collection, e.g. like this: For Each cb In dlg.CheckBoxes.
In Excel 97 or later the UserForm-object doesn’t group the controls in the same way.
Below you will find some example macros that shows how
you can change the value/content of several UserForm-controls:

Sub ResetAllCheckBoxesInUserForm()
Dim ctrl As Control
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "CheckBox" Then
            ctrl.Value = False
        End If
    Next ctrl
End Sub

Sub ResetAllOptionButtonsInUserForm()
Dim ctrl As Control
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "OptionButton" Then
            ctrl.Value = False
        End If
    Next ctrl
End Sub

Sub ResetAllTextBoxesInUserForm()
Dim ctrl As Control
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "TextBox" Then
            ctrl.Text = ""
        End If
    Next ctrl
End Sub

Users are saying about us...

  1. “This looks fine for a user defined form but what if someone has put form controls (as opposed to controls from the control toolbox) directly onto a spreadsheet?
    I have inherited a spreadsheet with about 40 drop down form controls and I need to programatically extract the values.
    Unfortunately they didn’t setup the cell link (Format Control->control->Cell link) and it doesn’t look like it will be practical to update all 150 instances of the spreadsheet…..

    Any hjelp appreciated.”

  2. “Since the form controls are part of the “”Shapes”” collection now, you could just use the following:

    Dim c As Shape

    For Each c In ActiveSheet.Shapes
    If c.FormControlType = xlDropDown Then

    ‘[do something like reading out the values]

    End If

    Next c

    I hope that helps.”

  3. “This looks fine for a user defined form but what if someone has put form controls (as opposed to controls from the control toolbox) directly onto a spreadsheet?
    I have inherited a spreadsheet with about 40 drop down form controls and I need to programatically extract the values.
    Unfortunately they didn’t setup the cell link (Format Control->control->Cell link) and it doesn’t look like it will be practical to update all 150 instances of the spreadsheet….. “

  4. “Since the form controls are part of the “”Shapes”” collection now, you could just use the following:

    Dim c As Shape

    For Each c In ActiveSheet.Shapes
    If c.FormControlType = xlDropDown Then

    ‘[do something like reading out the values]

    End If

    Next c

    I hope that helps.”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube