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

[Mitch_s_s]

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

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

[westconn1]

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

[Mitch_s_s]

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?

[Mitch_s_s]

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...

[westconn1]

try like this

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

[Mitch_s_s]

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!

[westconn1]

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

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

[Mitch_s_s]

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...

[Mitch_s_s]

Just checked, one of them is called Check Box 9

so it must be a forms control...?

[westconn1]

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