Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access Groups from IE? | New Users to Excel | |||
Checking and Unchecking CheckBoxes in code | Excel Discussion (Misc queries) | |||
checkboxes and code | Excel Programming | |||
Adding checkboxes via code to a workheet | Excel Programming | |||
Making All Checkboxes Run the Same Code | Excel Programming |