Arrays of Controls
 

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.

Arrays of Controls
 

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...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.



wrote:

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

Arrays of Controls
 

ps.

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

Dave Peterson wrote:

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...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.

wrote:

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

--

Dave Peterson

Arrays of Controls
 

This should get you started:
http://www.youtube.com/watch?v=b3kq9PDkGKM

Look on YouTube for other, similar, videos.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


" wrote:

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.
.

Arrays of Controls
 

Thanks - That will work great!

On Fri, 04 Jun 2010 17:34:57 -0500, Dave Peterson
wrote:


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

wrote:

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.