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
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.
"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."
"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..... "
When I enter this code into VB, it says 'User-defined type not defined'. how do i go about fixing this?
"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."
"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."
When I enter this code into VB, it says 'User-defined type not defined'. how do i go about fixing this?