vba code to access checkboxes within groups?
 

Hi all,
I have a question about the correct way to access the value of a
checkbox that has been 'grouped' with other checkboxes.

I have Excel 2003, on a windows XP sp2 platform.

I have a spreadsheet that has a number of checkboxes that have been
grouped to form a survey. The checkboxes were added using the control
toolbar.

There are about 30 checkboxes, grouped 5 per question, so there are 6
groups.

How can I use VBA to access the value of each checkbox?

I can do it if I first ungroup the checkboxes, then interrogate each
checkbox individually. I would rather not modify the spreadsheet!

Here is how I ungroup the checkboxes:
(straight from the MS example)

' ungroup the group boxes....
With ActiveSheet.Shapes
For s = .Count To 1 Step -1
If .Item(s).Type = msoGroup Then .Item(s).Ungroup
Next s
End With

And here is some code that lets me check each value...

For Each myCheckbox In Worksheets("mySheet").OLEObjects
If InStr(myCheckbox.name, "CheckBox") 0 Then
MsgBox ("I am "+myjunk.name)
If myCheckbox.Object.Value = False Then
' do something
Else
' do something else
End If
End If
Next

Any ideas on how to do this more cleanly?

Regards, Brad

vba code to access checkboxes within groups?
 

You might try accessing the value of the group. For ex. if the group name
was grpCheckBoxes1 the code would be grpCheckBoxes1.Value. This should get
you the value you are looking for.


" wrote:

Hi all,
I have a question about the correct way to access the value of a
checkbox that has been 'grouped' with other checkboxes.

I have Excel 2003, on a windows XP sp2 platform.

I have a spreadsheet that has a number of checkboxes that have been
grouped to form a survey. The checkboxes were added using the control
toolbar.

There are about 30 checkboxes, grouped 5 per question, so there are 6
groups.

How can I use VBA to access the value of each checkbox?

I can do it if I first ungroup the checkboxes, then interrogate each
checkbox individually. I would rather not modify the spreadsheet!

Here is how I ungroup the checkboxes:
(straight from the MS example)

' ungroup the group boxes....
With ActiveSheet.Shapes
For s = .Count To 1 Step -1
If .Item(s).Type = msoGroup Then .Item(s).Ungroup
Next s
End With

And here is some code that lets me check each value...

For Each myCheckbox In Worksheets("mySheet").OLEObjects
If InStr(myCheckbox.name, "CheckBox") 0 Then
MsgBox ("I am "+myjunk.name)
If myCheckbox.Object.Value = False Then
' do something
Else
' do something else
End If
End If
Next

Any ideas on how to do this more cleanly?

Regards, Brad