Excel: Checkbox - Application-defined or obkect-defined error

Hi,

I have the created the following procedure in Excel 2010, which should loop through each of the checkboxes on a given sheet and will expand of collapse depending on each checkbox's value

I am attempting to run the same procedure in Excel 2007, but I am receiving the above error.
I have checked that I have the same references added to the workbook and all appears to be ok.

Has anyone come across this issue before, or can you advise where I may be going wrong?

Thanks
Mitch

Code:

---

Sub expand_collapse_treatments()
Dim cell As Range
Dim shp As CheckBox
Application.ScreenUpdating = False




For Each shp In front.CheckBoxes
        If shp.Value = 1 Then
            Call show_hide_rows(shp.Caption, 1)
        Else
            Call show_hide_rows(shp.Caption, 0)
        End If
Next shp

Application.ScreenUpdating = True

End Sub

---

what is front?
if it is a worksheet, you need to loop through the shapes collection, then determine if each shape is a checkbox, then use the value accordingly

Thanks for your reply westconn1

This works on my personal PC with Office 2010, but not my work PC with Office 2010 and 2007.

I am looping through the checkbox collection, so I shouldn’t have to determine whether the shape is a checkbox should I?

Also,

front is a worksheet within the workbook

I have tried looping through the shapes too, but it does not appear to acknowledge the existence of the checkboxes...

try like this

vb Code:

Dim s As Worksheet, c As Shape
Set s = Sheets("sheet2")
For Each c In s.Shapes
    If c.OLEFormat.Object.ProgId = "Forms.CheckBox.1" Then
        ' do stuff
        chked = c.OLEFormat.Object.Object.Value
        MsgBox c.OLEFormat.Object.Object.Caption & vbNewLine & chked
    End If
Next

Thanks for your reply, I've used your code to loop through the objects on my front sheet

When it gets to a drop down or checkbox it fails saying

Object does not support this property or method

I'm stumped!

what type of checkboxes are they?
are controls or forms checkboxes?
the default name for an added control would be like CheckBox1, where as a froms checkbox would be like Check Box 1
they are different of different type
c.type = 12 for a control or 8 for a forms checkbox
the code i posted is to work with controls

try inserting the extra line

vb Code:

For Each c In s.Shapes
      If c.Type = 8 Then MsgBox c.OLEFormat.Object.Value = 1: Exit Sub

Same message when I try that code

I can't remember whether I chose activeX or Form controls, but I am prompted to load ActiveX controls when I load the message...

Just checked, one of them is called Check Box 9

so it must be a forms control...?

Quote:

---

so it must be a forms control...?

---

looks like,
i tested the code i posted above in excel 2000, afaik there should be no problem with it running in excel 2007, but i believe controls have some other name in the later version