Arrays of Controls

[Guest]

I would like to create a form with a large number of checkboxes. I would
like to be able to set their values and possibly other properties and
retrieve their values in a look rather than have long strings of code
reference each checkbox bt name. Is there any way to accomplish that?

Thank you.

 

[Dave Peterson]

I think the answer is gonna depend on what kind of checkboxes these are and
where they're located.

If they're checkboxes from the Forms toolbar and they're placed on a sheet, you
could loop through them with something like this:

Option Explicit
Sub testme()
dim cbx as checkbox
for each cbx in worksheets("Somesheetnamehere").checkboxes
msgbox cbx.name & vblf & cbx.value
next cbx
end sub

If they're checkboxes from the control toolbox toolbar placed on a worksheet:

Option Explicit
Sub testme()
Dim OLEObj as oleobject
for each oleobj in worksheets("Somesheetnamehere").oleobjects
if typeof oleobj.object is msforms.checkbox then
msgbox oleobj.name & vblf & oleobj.object.value
end if
next oleobj
end sub

If the checkbox is on a userform (designed in the VBE, not just a worksheet set
up to look like a form), the code is very close to the OLEObject code:


Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.CheckBox Then
MsgBox ctrl.Name & vbLf & ctrl.Value
End If
Next ctrl
End Sub

======
If you wanted to use common code for either the checkboxes from the control
toolbox toolbar (placed on a sheet) or the checkboxes on a userform, you can use
this technique from John Walkenbach:

http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure/

He uses Commandbuttons in his example, but the code would be very similar.

=======
If you wanted to use common code for the checkboxes from the Forms toolbar
(placed on a worksheet), you can just assign the same macro to each of the
checkboxes.

 

[Dave Peterson]

ps.

VBA doesn't allow you to use this kind of control array (like VB???).

 

[ryguy7272]

This should get you started:

Look on YouTube for other, similar, videos.

 

[Guest]

Thanks - That will work great!

If the checkbox is on a userform (designed in the VBE, not just a worksheet set
up to look like a form), the code is very close to the OLEObject code:


Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.CheckBox Then
MsgBox ctrl.Name & vbLf & ctrl.Value
End If
Next ctrl
End Sub