Locking CheckBoxes?

[austin350s10]

What is the vba code to lock all the checkboxes on the active worksheet?

I tried the following but not luck:

ActiveSheet.CheckBoxes = Locked

I am using Form Controls CheckBoxes not ActiveX checkBoxes.

 

[Smitty]

How's this:

Sub foo()
   Dim sh As Shape
        For Each sh In ActiveSheet.Shapes
            sh.Locked = True
        Next sh
End Sub

HTH,

 

[Colin Legg]

    If ActiveSheet.CheckBoxes.Count > 0 Then
        ActiveSheet.CheckBoxes.Locked = True
    End If

 

[austin350s10]

I was really looking for a way to lock the checkboxes so the user cannot check them. Didn't realize the locked property just prevents the user from moving the ceckboxes around.

Is there a way to do this?

 

[Colin Legg]

Yep.

ActiveSheet.CheckBoxes.Enabled = False

 

[austin350s10]

That seems to work on a test workbook but pulls an error when running on my actual workbook. The only thing I can think of is some of the checkboxes have there visible property set to false. Would I need to make them visible first then set the enable property?

If so is there a quick way to set all the checkboxes to visible = true

 

[Colin Legg]

Yes, you're right - the checkboxes must be visible first. So, if we put it all together we get:

    If ActiveSheet.CheckBoxes.Count > 0 Then
        ActiveSheet.CheckBoxes.Visible = True
        ActiveSheet.CheckBoxes.Enabled = False
    End If

You would also get an error if the activesheet is protected (with edit objects not checked).

Hope that helps...

 

[austin350s10]

Cant figure out the problem. First I unprotect the ActiveSheet then run the following line:

ActiveSheet.CheckBoxes.Visible = True

Each time the script gets to this line I get a '1004' eror

Any ideas why this would happen?

 

[Colin Legg]

Is it preceded by a check that there are some checkboxes like I did in my code?

If ActiveSheet.CheckBoxes.Count > 0 Then