Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with arrays (transferring values between two arrays) | Excel Programming | |||
Losing data in controls (and arrays) during unhandled exceptions. | Excel Programming | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Event procedures for controls added with Controls.Add | Excel Programming |