Small issue with clearing checkboxes

[sn281]

Hi all,

Thanks for reading.

I have what I think is a very easy issue (annoyingly I cannot seem to solve it ). I have some code that works and resets all my checkboxes in my worksheet, and now I've got another worksheet that I would like to split out resetting different columns. This way I can reset Tuesday's checkboxes, without impacting Monday's for example (it's a kind of to-do list).

So I'll post the code that works for the whole sheet below, but if I wanted that code to reset the range of D3:D13 for example, how do I add that in?

I've tried for example: For Each Cbox In Worksheets("ToDo").Range("D3:D13").CheckBoxes
But it won't work

Thanks so much in advance.

(it's Form control rather than ActiveX if that helps)


My code:

Sub Clear_Checks()


Dim Cbox As Excel.CheckBox


Sheets("ToDo").Select
For Each Cbox In ActiveSheet.CheckBoxes
        Cbox.Value = xlOff
Next Cbox



End Sub

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim Cbox As Excel.CheckBox
    For Each Cbox In Worksheets("ToDo").CheckBoxes
        With Cbox
            If Not Application.Intersect(.TopLeftCell, Worksheets("ToDo").Range("D3:D13")) Is Nothing Then
                .Value = xlOff
            End If
        End With
    Next Cbox
End Sub

 

[mole999]

I expect each check box to have a unique ID, so for doing a small selection of values you might need to address them individually, If Monday was 1 to 20 all could be done in that sequences with a loop

 

[sn281]

Thanks for your responses. I've tried your code Andrew, but it seems to set them to True as opposed to False, so then I removed the "Not" from the IF statement. This then worked, however it did it for every cell not just within the range specified?

And I agree with you mole999, however the only issue is that if I add a checkbox to Monday after having made the sheet then it will have a massively different number so I can't just loop through adding 1 each time.

Unless can I write something that returns the name of the checkboxes in the range and then say reset them all to false?

Thanks a lot all.

 

[Andrew Poulsom]

The code worked for me when I tested it. If the top left cell of the CheckBox is in the range D3:D13 it is unchecked (xlOff). Otherwise it remains as it was.

 

[sn281]

Sorry Andrew, that's completely my bad. Your code works perfectly, thanks so so mcuh.

(I moved the column along one and completely forgot about it!).

Thanks so much!!