Processing checkboxes using vb

ramich

New Member
Joined
Sep 30, 2006
Messages
3
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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
L

Legacy 98055

Guest
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

New Member
Joined
Sep 30, 2006
Messages
3
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.
 
L

Legacy 98055

Guest
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="filter:progid: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
 

Forum statistics

Threads
1,136,346
Messages
5,675,228
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top