ActiveX control checkbox to (un-)check all checkboxes

**Stefan1983** · 08-31-2012, 02:54 AM

Hello,

I'm using MS Excel 2003 and I have a sheet with altogether 6 ActiveX checkboxes. I want to have a 7th that (un-)checks them all. I can do that for form controls in VBA but not for ActiveX. Here's my code for the form controls:

Sub CheckAll()
    Dim lngIndex As Long
    Dim lngState As Long
    
    lngState = ActiveSheet.Shapes("Check Box 7").ControlFormat.Value
    For lngIndex = 1 To 6
        With ActiveSheet
            With .Shapes("Check Box " & lngIndex)
                .ControlFormat.Value = lngState
            End With
        End With
    Next
    
End Sub

Unfortunately, I have to stick with ActiveX controls because I need them to trigger a worksheet change event.
Thanks.
Stefan

**Andy Pope** · 08-31-2012, 03:50 AM

Sub CheckAll()
    Dim lngIndex As Long
    Dim blnState As Boolean
    
    blnState = ActiveSheet.Shapes("CheckBox7").OLEFormat.Object.Object.Value
    For lngIndex = 1 To 6
        With ActiveSheet
            With .Shapes("CheckBox" & lngIndex)
                .OLEFormat.Object.Object.Value = blnState
            End With
        End With
    Next
    
End Sub

**Stefan1983** · 08-31-2012, 04:09 AM

Thanks, Andy. Works great.

**Stefan1983** · 08-31-2012, 05:10 AM

Is it actually possible to have the 7th checkbox unchecked as soon as one of the others is unchecked.

**Andy Pope** · 08-31-2012, 05:25 AM

Yes. You need to check the state of the checkbox being altered and if unset you can also unset CB7.

Standard code module

Public g_blnUpdating As Boolean

Sub CheckAll()
    Dim lngIndex As Long
    Dim blnState As Boolean
    
    blnState = ActiveSheet.Shapes("CheckBox7").OLEFormat.Object.Object.Value
    
    g_blnUpdating = True
    
    For lngIndex = 1 To 6
        With ActiveSheet
            With .Shapes("CheckBox" & lngIndex)
                .OLEFormat.Object.Object.Value = blnState
            End With
        End With
    Next
    
    g_blnUpdating = False
    
End Sub

Worksheet object

Private Sub CheckBox1_Click()

    If g_blnUpdating Then Exit Sub
    
    If Not CheckBox1.Value Then
        g_blnUpdating = True
        CheckBox7.Value = False
        g_blnUpdating = False
    End If
    
End Sub
Private Sub CheckBox2_Click()

    If g_blnUpdating Then Exit Sub
    
    If Not CheckBox2.Value Then
        g_blnUpdating = True
        CheckBox7.Value = False
        g_blnUpdating = False
    End If
    
End Sub


Private Sub CheckBox3_Click()

    If g_blnUpdating Then Exit Sub
    
    If Not CheckBox3.Value Then
        g_blnUpdating = True
        CheckBox7.Value = False
        g_blnUpdating = False
    End If
    
End Sub
Private Sub CheckBox4_Click()

    If g_blnUpdating Then Exit Sub
    
    If Not CheckBox4.Value Then
        g_blnUpdating = True
        CheckBox7.Value = False
        g_blnUpdating = False
    End If
    
End Sub
Private Sub CheckBox5_Click()

    If g_blnUpdating Then Exit Sub
    
    If Not CheckBox5.Value Then
        g_blnUpdating = True
        CheckBox7.Value = False
        g_blnUpdating = False
    End If
    
End Sub

Private Sub CheckBox6_Click()

    If g_blnUpdating Then Exit Sub
    
    If Not CheckBox6.Value Then
        g_blnUpdating = True
        CheckBox7.Value = False
        g_blnUpdating = False
    End If
    
End Sub

Private Sub CheckBox7_Click()
    
    If g_blnUpdating Then Exit Sub
    CheckAll
    
End Sub