Use VBA to change worksheet tab color based on ActiveX checkbox values

Hello -

I would like to use VBA to change the worksheet tab color based on ActiveX checkbox values. I have 10 ActiveX checkboxes on one worksheet. When one of the boxes is checked, I want the worksheet tab color to change to yellow. When all are checked, I would like the worksheet tab to change to green. When none are checked, I would like the worksheet tab to remain red.

What is the best way to do this?

Thank you!

Sub CheckStatus()
Dim Ck As Object
Dim N As Long

For Each Ck In ActiveSheet.OLEObjects
If TypeName(Ck.Object) = "CheckBox" Then
    If Ck.Object.Value Then
        N = N + 1
    End If
End If
Next

Select Case N
    Case 0
        ActiveSheet.Tab.ColorIndex = 3
    Case 1 To 9
        ActiveSheet.Tab.ColorIndex = 6
    Case 10
        ActiveSheet.Tab.ColorIndex = 4
End Select

End Sub

Go to Developer tab. Click Design. Double click on each button. This will generate a click event. In each event type the name of the macro. Turn design mode off. Then as you click each checkbox, the code will run and change to tab color as required. I thought it didn't work at first either, but you have to get off the tab to really see the color.

For checkboxes in Groups

Sub CheckStatus()
Dim WS As Worksheet
Dim N As Long
Dim Grp As Shape
Dim Ck As Shape

Set WS = ActiveSheet

For Each Grp In WS.Shapes
    If Grp.Type = msoGroup Then
        For Each Ck In Grp.GroupItems
        If TypeName(Ck.OLEFormat.Object.Object) = "CheckBox" Then
            If Ck.OLEFormat.Object.Object.Value Then
                N = N + 1
            End If
        End If
        Next
    End If
Next

Select Case N
    Case 0
        ActiveSheet.Tab.ColorIndex = 3
    Case 1 To 9
        ActiveSheet.Tab.ColorIndex = 6
    Case 10
        ActiveSheet.Tab.ColorIndex = 4
End Select

End Sub