Processing checkboxes using vb

[ramich]

Hi,

I would like to have several checkboxes on a spreadsheet, then, by VB to insert the caption of all the checked checkboxes to an array.

Can you help with that?

 

[Legacy 98055]

Hi Ramich.

Would you please widen your explanation with some more details? Are these checkboxes from the Forms commandbar or the Control Toolbox commandbar? Also, please define what you are considering when speaking of an array. A simple string() array in VBA? A collection? Something else?

Thanks

 

[ramich]

HI Tom,
the checkboxes are from the 'Control Toolbox'.
I'dd like them to be a part of the sheet (not to be generated by VB).
So the user would see them and check each one he needs. then i need to find (by loop) which was checked and to insert theirs captions (one by one) to a string array.

 

[Legacy 98055]

Hi Ramich. Here is one of at least several ways you might accomplish this task. Download the example for a better look.

ramich 1131115.zip

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> MyCaptions() <font color="#0000A0">As</font> <font color="#0000A0">String</font>

      <font color="#008000"> 'takes a look at each CheckBox in some worksheet. "Sheet1" is a reference to the</font>
      <font color="#008000"> 'worksheet that contains your checkboxes. MyCaptions() is an uninitialized</font>
      <font color="#008000"> 'string array variable that will be filled if any checkboxes are located and are checked</font>
       <font color="#0000A0">If</font> ReturnCheckedCaptions(Sheets("Sheet1"), MyCaptions()) <font color="#0000A0">Then</font>
          <font color="#008000"> 'eligible items were loaded into your string array</font>
           <font color="#0000A0">Dim</font> x, s

           s = "Items is your array..." & vbCrLf & vbCrLf
           <font color="#0000A0">For</font> x = 0 <font color="#0000A0">To</font> UBound(MyCaptions)
               s = s & MyCaptions(x) & vbCrLf
           <font color="#0000A0">Next</font> x
           MsgBox s
       <font color="#0000A0">Else</font>
          <font color="#008000"> 'not eligible items were located (None were checked). You array is empty.</font>
           MsgBox "No items were located. Your array is empty..."
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Function</font> ReturnCheckedCaptions(sh <font color="#0000A0">As</font> Worksheet, <font color="#0000A0">ByRef</font> MyCaptions() <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
       <font color="#0000A0">Dim</font> c <font color="#0000A0">As</font> OLEObject, x <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>

      <font color="#008000"> 'this variable will not change unless one or more eligible items have been located</font>
       x = -1

       <font color="#0000A0">For</font> <font color="#0000A0">Each</font> c <font color="#0000A0">In</font> Me.OLEObjects
           <font color="#0000A0">If</font> c.ProgId = "Forms.CheckBox.1" <font color="#0000A0">Then</font>
              <font color="#008000"> 'Value refers to (Checked=True or UnChecked=False)</font>
               <font color="#0000A0">If</font> c.Object.Value <font color="#0000A0">Then</font>
                   x = x + 1
                   <font color="#0000A0">ReDim</font> <font color="#0000A0">Preserve</font> MyCaptions(x)
                   MyCaptions(x) = c.Object.Caption
               <font color="#0000A0">End</font> <font color="#0000A0">If</font>
           <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       <font color="#0000A0">Next</font>

      <font color="#008000"> 'returns true if one or more items have been loaded into the array</font>
       ReturnCheckedCaptions = (x <> -1)
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("10120069535331").value=document.all("10120069535331").value.replace(/<br \/>\s\s/g,"");document.all("10120069535331").value=document.all("10120069535331").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("10120069535331").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="10120069535331" wrap="virtual">
Sub Example()
Dim MyCaptions() As String

'takes a look at each CheckBox in some worksheet. "Sheet1" is a reference to the
'worksheet that contains your checkboxes. MyCaptions() is an uninitialized
'string array variable that will be filled if any checkboxes are located and are checked
If ReturnCheckedCaptions(Sheets("Sheet1"), MyCaptions()) Then
'eligible items were loaded into your string array
Dim x, s

s = "Items is your array..." & vbCrLf & vbCrLf
For x = 0 To UBound(MyCaptions)
s = s & MyCaptions(x) & vbCrLf
Next x
MsgBox s
Else
'not eligible items were located (None were checked). You array is empty.
MsgBox "No items were located. Your array is empty..."
End If

End Sub

Function ReturnCheckedCaptions(sh As Worksheet, ByRef MyCaptions() As String) As Boolean
Dim c As OLEObject, x As Integer

'this variable will not change unless one or more eligible items have been located
x = -1

For Each c In Me.OLEObjects
If c.ProgId = "Forms.CheckBox.1" Then
'Value refers to (Checked=True or UnChecked=False)
If c.Object.Value Then
x = x + 1
ReDim Preserve MyCaptions(x)
MyCaptions(x) = c.Object.Caption
End If
End If
Next

'returns true if one or more items have been loaded into the array
ReturnCheckedCaptions = (x <> -1)
End Function</textarea>

ramich 1131115.zip

 

[ramich]

Tom,
Tanks a lot, it's exactly what i needed.
Rami