VBA Prevent save with checkboxes

[crazybuckeyeguy]

I am trying to set the following VBA to prevent save if any checkboxes are empty. Any help is great.

Private Sub dontsave()
If Sheets("Sheet1").Range("A3") = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End Sub

 

[mikerickson]

What kind of checkboxes? (ActiveX or forms control) Are they all on one sheet?

 

[crazybuckeyeguy]

What kind of checkboxes? (ActiveX or forms control) Are they all on one sheet?

I was going to use Form controls. Would active x be more viable?

 

[mikerickson]

If you use forms checkboxes, you could use this code.
Put this in the ThisWorkbook code module

' in ThisWorkbook code module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = Not (AllChecked)
    If Cancel Then MsgBox "save aborted"
End Sub

Function AllChecked(Optional SheetToCheck As Worksheet) As Boolean
    Dim oneShape As Object
    If SheetToCheck Is Nothing Then Set SheetToCheck = ThisWorkbook.ActiveSheet
    
    AllChecked = True
    For Each oneShape In SheetToCheck.Shapes
        With oneShape
            If .Type = msoFormControl Then
                If .FormControlType = xlCheckBox Then
                    AllChecked = AllChecked And (.ControlFormat.Value = xlOn)
                End If
            End If
        End With
    Next oneShape
End Function

 

[crazybuckeyeguy]

If you use forms checkboxes, you could use this code.
Put this in the ThisWorkbook code module

' in ThisWorkbook code module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = Not (AllChecked)
    If Cancel Then MsgBox "save aborted"
End Sub

Function AllChecked(Optional SheetToCheck As Worksheet) As Boolean
    Dim oneShape As Object
    If SheetToCheck Is Nothing Then Set SheetToCheck = ThisWorkbook.ActiveSheet
    
    AllChecked = True
    For Each oneShape In SheetToCheck.Shapes
        With oneShape
            If .Type = msoFormControl Then
                If .FormControlType = xlCheckBox Then
                    AllChecked = AllChecked And (.ControlFormat.Value = xlOn)
                End If
            End If
        End With
    Next oneShape
End Function

It worked perfectly. Thank you so much.