Arrays of Controls
in [Programming]
|
From: NoSpam on 4 Jun 2010 16:41 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.
From: Dave Peterson on 4 Jun 2010 18:34 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. NoSpam(a)aol.com 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
From: Dave Peterson on 4 Jun 2010 18:39 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/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. > > NoSpam(a)aol.com 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
From: ryguy7272 on 4 Jun 2010 18:47 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''. "NoSpam(a)aol.com" 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. > . >
From: NoSpam on 4 Jun 2010 18:49 Thanks - That will work great! On Fri, 04 Jun 2010 17:34:57 -0500, Dave Peterson <petersod(a)verizonXSPAM.net> 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 >NoSpam(a)aol.com 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.
|
Pages: 1 Prev: eliminate leading quote in SAP download Next: Find and Replace with contents of cell |