ActiveSheet.CheckBoxes.Value = False

[NX555V]

Hello

How do I make the macro check to see if the boxes are checked/not checked before hand?

So if they are checked, then it unchecks them : if unchecked then it checks them


Also,

How would I assign a range of check boxes so that it does the above, not for all checkboxes on the sheet but for a specific range?

Thanks!

 

[mikerickson]

Are these ActiveX checkboxes or are they from the Forms menu?
Controls aren't in cells. Like Shapes, they're in a "layer" above the cells.
Is "upper left corner of the control above a cell in the specified range" an adiquate condition for your situation?
Or would you need "all portions of the control are above a cell in the specified range"?

 

[NX555V]

The checkboxes are form control checkboxes. The checkboxes are along S27 to X27 and another set about 15 rows below in the same columns. Thanks for your help

 

[lenze]

Just curious! What happens when a checkbox is TRUE? FALSE?
lenze

 

[NX555V]

When the boxes are true and false they fill out a chart. I have 19 checkboxes for individual sets of data. I want to be able to check them all on and off in one shot.

Thanks

 

[mikerickson]

These subs might help

Sub allChkBoxesOn()
    Call TurnAllCheckBoxes(True)
End Sub

Sub allChkBoxesOff()
    Call TurnAllCheckBoxes(False)
End Sub

Sub SomeChkBoxesOn()
    Dim ChangeBoxesInRange As Range
    Set ChangeBoxesInRange = Range("a1:D30")
    Call TurnAllCheckBoxes(True, ChangeBoxesInRange)
End Sub

Sub SomeChkBoxesOff()
    Dim ChangeBoxesInRange As Range
    Set ChangeBoxesInRange = Range("a1:D30")
    Call TurnAllCheckBoxes(False, ChangeBoxesInRange)
End Sub

Sub TurnAllCheckBoxes(newValue As Boolean, Optional withinRange As Range)
    Dim oneShape As Object
    Dim newFormsValue As Long
    If withinRange Is Nothing Then Set withinRange = ActiveSheet.Cells
    newFormsValue = IIf(newValue, xlOn, xlOff)
    With withinRange.Parent
        For Each oneShape In .Shapes
            With oneShape
                If (Not Application.Intersect(.TopLeftCell, withinRange) Is Nothing) _
                    And (Not Application.Intersect(.BottomRightCell, withinRange) Is Nothing) Then
                If .Type = msoFormControl Then
                    If .FormControlType = xlCheckBox Then
                        .OLEFormat.Object.Value = newFormsValue
                    End If
                End If
                End If
            End With
        Next oneShape
    End With
End Sub