Macro needed to uncheck certain check boxes

[richardcdahlgren]

I have cells that read False when a condition isn't met. I need a macro that will uncheck check boxes in cells M5-M17 when cell AE6 does not equal False. Is there a way to do that with a macro? Or is it only uncheck all the boxes on the worksheet?

Thanks in advance for all the help!

 

[richardcdahlgren]

I should add that the check boxes are Form Control and they are Check Boxes 11-18. And AE6 is either True or False. So Macro where AE6 is True to uncheck check boxes 11 thru 18.

 

[GWteB]

To be pasted into the module of the worksheet to be affected...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("AE6")) Is Nothing Then
        If VarType(Range("AE6").Value2) <> vbBoolean Or Range("AE6").Value2 <> False Then
            UnCheckCBs argSht:=Target.Parent
        End If
    End If
End Sub


Sub UnCheckCBs(ByVal argSht As Worksheet)

    Dim Shp     As Shape
    Dim isCB    As Boolean
    Dim n       As Long

    For Each Shp In argSht.Shapes
        On Error Resume Next
        isCB = (Shp.FormControlType = xlCheckBox)
        On Error GoTo 0
        If isCB Then
            n = CLng(Replace(Shp.Name, "Check Box", ""))
            If n >= 11 And n <= 18 Then
                Shp.OLEFormat.Object.Value = xlOff
            End If
            isCB = False
        End If
    Next Shp
End Sub

 

[richardcdahlgren]

To be pasted into the module of the worksheet to be affected...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("AE6")) Is Nothing Then
        If VarType(Range("AE6").Value2) <> vbBoolean Or Range("AE6").Value2 <> False Then
            UnCheckCBs argSht:=Target.Parent
        End If
    End If
End Sub


Sub UnCheckCBs(ByVal argSht As Worksheet)

    Dim Shp     As Shape
    Dim isCB    As Boolean
    Dim n       As Long

    For Each Shp In argSht.Shapes
        On Error Resume Next
        isCB = (Shp.FormControlType = xlCheckBox)
        On Error GoTo 0
        If isCB Then
            n = CLng(Replace(Shp.Name, "Check Box", ""))
            If n >= 11 And n <= 18 Then
                Shp.OLEFormat.Object.Value = xlOff
            End If
            isCB = False
        End If
    Next Shp
End Sub

I may not be using it right, but when I did copy/paste into Module 1 it asked me to name it when I tried to run it. When I tried to name it, it added the name i just gave it to the bottom. And I am not familiar with the Option Explicit top line. As far as what it looks like its should do - it looks great and a very clever way to make it work - but I just can't get it to start.

Thank you.

 

[GWteB]

Okay, I see.
Module 1 is usually a standard module. As I said, the code is supposed to be pasted into a worksheet module, more specific: the module of the worksheet to be affected.
To get it right, activate your sheet with the check boxes, right click on its tab and click View Code. The VBE will open with the right code module active on the main (right hand) pane. VBE's title bar will confirm wether you've opened the correct code module, see attached image. Pressing CTRL-R opens the Project Explorer, the left hand pane on the image (as you can see there is in this example no standard module present).

You don't have to run this code manually, it runs automagically. The Worksheet_Change procedure is a so called event procedure and will be executed by Excel when such a change event on that particular worksheet occurs. When Excel invokes this event procedure, Excel needs to pass an argument (also called parameter) to this procedure (making it impossible to run this code indepently using F5 key within the VBE). This requirement is visible because of the procedures head line, the arguments needed are between the parentheses. This event procedure expects a Range object (one worksheet cell or a group of cells) as only parameter and names it Target for further reference/use. Obviously it's not just any Range object rather the Range object whose value (more specific: Formula property) just has been changed.

The provided code within this procedure determines wether in such a change event cell AE6 is involved. If that's the case, code checks wether the new value within cell AE6 is the right Boolean value for invoking the procedure to uncheck the desired Check Boxes (Form Controls are expected, otherwise the code errors out).
The UnCheckCBs procedure actually does the desired job. This procedure also has a dependency: a (random) Worksheet object (to look on for the existence of check boxes to manipulate). This way this code is portable, usable for any worksheet. To be multi-usable, the UnCheckCBs procedure must be placed in a standard module.

Finally, for the Option Explicit statement, take a look over here:

Option Explicit in Excel VBA

We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.

www.excel-easy.com

 

[richardcdahlgren]

Thank you very much for taking the time to explain and to write the code. You have no idea how happy you have made me as well as more enlightened on the process!!

 

[GWteB]

My pleasure Enjoy!