Loop for ChechBoxes 1

[cdulong]

Im trying to create a loop that will go through a list of 27 Check boxes and perform a specific action based on the value of the check box. I think I am close with my script but I keet getting an error when I execute. Mismatch error.

Could someone take a look at my logic and let me kow where i am going wrong.

Thanks,
cdulong

Private Sub CommandButton1_Click()
Dim boxnum As Integer
Dim Rng As Range
Dim cb As CheckBox


boxnum = 0

For Each Rng In Range("A5:A31")
boxnum = boxnum + 1
cb = "CheckBox" & boxnum
'MsgBox cb, vbOKCancel

If cb.Value = True Then
'MsgBox "True", vbOKOnly
Sheets("PP#" & boxnum).Unprotect ("admin")
Sheets("PP#" & boxnum).Range("A11:O28").Locked = True
Sheets("PP#" & boxnum).Range("Q11:Q28").Locked = True
Sheets("PP#" & boxnum).Range("B31:Q31").Locked = True
Sheets("PP#" & boxnum).Protect ("admin")
End If
If cb.Value = False Then
'MsgBox "True", vbOKOnly
Sheets("PP#" & boxnum).Unprotect ("admin")
Sheets("PP#" & boxnum).Range("A11:O28").Locked = False
Sheets("PP#" & boxnum).Range("Q11:Q28").Locked = False
Sheets("PP#" & boxnum).Range("B31:Q31").Locked = False
Sheets("PP#" & boxnum).Protect ("admin")
End If

Next Rng

MsgBox "Finished Update!!", vbOKOnly


End Sub

 

[SkipVought]

Hi,

rng does absolutely nothing for this procedure.

Private Sub CommandButton1_Click()
    Dim boxnum As Integer
    Dim cb As Shape
    
    For boxnum = 1 To 2 '[b]<<--put your CheckBox count LIMIT here[/b]
        Set cb = Shapes("CheckBox" & boxnum)
        'MsgBox cb, vbOKCancel
        
        If cb.OLEFormat.Object.Object.Value Then
            'MsgBox "True", vbOKOnly
            Sheets("PP#" & boxnum).Unprotect ("admin")
            Sheets("PP#" & boxnum).Range("A11:O28").Locked = True
            Sheets("PP#" & boxnum).Range("Q11:Q28").Locked = True
            Sheets("PP#" & boxnum).Range("B31:Q31").Locked = True
            Sheets("PP#" & boxnum).Protect ("admin")
        Else
            'MsgBox "True", vbOKOnly
            Sheets("PP#" & boxnum).Unprotect ("admin")
            Sheets("PP#" & boxnum).Range("A11:O28").Locked = False
            Sheets("PP#" & boxnum).Range("Q11:Q28").Locked = False
            Sheets("PP#" & boxnum).Range("B31:Q31").Locked = False
            Sheets("PP#" & boxnum).Protect ("admin")
        End If
        
    Next
    
    MsgBox "Finished Update!!", vbOKOnly
    
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Well first of all

cb = "CheckBox" & boxnum

is wrong. You declare cb as Checkbox (and I suspect that is not good enough, you may need MSForms.Checkbox).

So it should probably be:

Set cb = "CheckBox" & boxnum

However, depending on where (what module) you have the code, CheckBoxx may not be good enough. It may need to be fully qualified.

Though i think it is your cb = "CheckBox" & boxnum

This seems to be a string, but

If cb.Value = True

implies an object. Try adding the Set.

Gerry

 

[cdulong]

Thanks Skip!!!

That is exactly what I was going for.

cdulong