Replace VBA code that incrementally checks 3 checkboxes to False/True

[Soraka]

I have below a VBA code that checks 3 checkboxes one after the other, everytime a specific word appears in a cell.
Because my workbook is slowing down a lot due to the heavy amount of checkboxes I have, I want to see what the performances would be if I were to replace those checkboxes by a False/True statement according the number of times the specific word appears.

Here is the code I use and is working, once the specific word appears, it will call the Sub below:

Sub TickCheckBox(rng As Range)
Dim CB As CheckBox, cb1 As CheckBox, cb2 As CheckBox, cb3 As CheckBox
r = rng.Row
With rng.Parent
    For Each CB In rng.Parent.Checkboxes
        Select Case CB.TopLeftCell.Address
            Case .Cells(rng.Row, "CX").Address
                Set cb1 = CB
            Case .Cells(rng.Row, "CY").Address
               Set cb2 = CB
            Case .Cells(rng.Row, "CZ").Address
                Set cb3 = CB
        End Select
     Next
     If cb1.value = xlOff Then
        cb1.value = xlOn
     ElseIf cb2.value = xlOff Then
        cb2.value = xlOn
    ElseIf cb3.value = xlOff Then
        cb3.value = xlOn
        blnThreeFound = True
    End If
End With
End Sub

The 3 checkboxes are in columns CX, CY and CZ, 24 in each column, so 72 checkboxes.
I would like to replace them by False/True hoping it elaviates the sluggishness.

 

[StephenCrump]

If you're running this in Excel 365, why not switch to the new in-cell checkboxes?

And if you're happy for the checkboxes to be checked/unchecked based on formulae (i.e rather than allowing the user to click), you could bypass VBA and use an appropriate formula instead.

	A	B
1		Hello
Sheet1

Cell Formulas
Range	Formula
A1	A1	=B1="Hello"

 

[Soraka]

If you're running this in Excel 365, why not switch to the new in-cell checkboxes?

And if you're happy for the checkboxes to be checked/unchecked based on formulae (i.e rather than allowing the user to click), you could bypass VBA and use an appropriate formula instead.

	A	B
1		Hello
Sheet1

Cell Formulas
Range	Formula
A1	A1	=B1="Hello"

View attachment 123503

I made the change today for 365, so I am "discovering", from Office 2021.
Fortunately my xlsm can still be read and everything looks in order and playable (functions/vba//loop and activable).

Although I am unfamiliar with what you said.
I have a ton of checkboxes that get checked//unchecked upon button clicks that I created.
These checkboxes take a while to be checked / unchecked, 1 by 1, I can see them.
It is a little faster than from 2021, but the vast majority of the waiting time has not changed.

I would like to try your 2 solutions as I like the checkbox visual but would love to remove the freezes and delays.

As of now, I have buttons and checkboxes from Developper/Insert//Form Control.
The button calls a Sub from a Module that will check/uncheck the CB.

I have not learned how to do otherwise so far.

 

[StephenCrump]

The code you

I would like to try your 2 solutions as I like the checkbox visual but would love to remove the freezes and delays.

It sounds like you might have a lot of other code running as well.

There are ways you can use code to organise checkboxes more efficiently, but I'd start by asking whether you really need so many in the first place.

The particular code you've posted is inefficient, as potentially it loops through every checkbox in the worksheet to find the particular three you want to change. It would be better to refer to them direct by name. The new Excel 365 checkboxes may help here, as you can refer to the correct one by its row number.