Change the value/content of several UserForm-controls

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

Document last updated 2000-02-05 12:47:20 Printerfriendly version

User comments:

Darsheet Pandya from Himatnagar, Dist: Sabarkantha, State: Gujarat, India wrote (2005-07-01 19:54:10 CET):

Wants to Learn more
I really do want to learn how use userforms and to most effective macros please send me material which make me powerful to build macros.

Ole P. from Norway wrote (2005-04-05 18:17:37 CET):

Re: Thanks a Ton!!!!!
You can store the data from your user form in a hidden worksheet like this:
Private Sub btnOK_Click()
With Worksheets("MyHiddenSheet")
.Range("A1").Formula = Me.TextBox1.Text
.Range("A2").Formula = Me.TextBox2.Text
.Range("A3").Formula = Me.TextBox3.Text
End With
End Sub

You can hide and unhide worksheets from the menu Format, Sheet, Hide/Unhide, or you can do it using the properties window in the VBE.

Komal from Mumbai, India wrote (2005-04-05 10:14:03 CET):

Thanks a Ton!!!!!
I was in a deadline to automate the excels and was in a real mess working out on forms so it was a real help!!!. But i am in need of some more help.

I want to make a form for data entry and update it into the sheets without giving the user any acess to the sheet ..Can you pls help me

ATIQ from Karachi, Pakistan wrote (2005-01-04 21:02:42 CET):

BRAVO!!! - I Found the best website which can help me alot.
After searching of 3 hours I found this site which is very useful for biggners and can improver their VBA (Excel) Programming.

I could not find VBA categorized codes like on this site than others i have visited before.

I really appreciate Team leaders and member of this website which is deloped after great efforts.

Ole P. from Norway wrote (2004-06-21 11:04:12 CET):

Re: Updating required
Thanks for the suggestion.
No update is required since the code runs just fine as it is in all versions of Excel from 97 to 2003 as long as the code is placed in the UserForm module.

Peter Kolada from Erkelenz, close to Duesseldorf, Germany wrote (2004-06-20 16:03:33 CET):

Updating required
May I suggest that the above document to be updated in order to be compatible with EXcel 2000 and above. I think the keyword MSFORMS must be included in the code.

Sorry, but I don't have the time to provide a complete example.

I think your sites provide excellent support if you have to grapple with VBA.

Alok from Los Angeles wrote (2004-06-16 20:17:40 CET):

Superb Examples
Very good examples.. I think yours is one of the best sites to lear VBA