Consolidate checkbox macros

[SensualCarrots]

I'm wondering if anyone can help me consolidate this. I have 59 checkboxes on a worksheet. They are all ActiveX controls that run the following code. Each is unique. The purpose is if the box is unchecked, it unlocks the sheet, and changes the Locked property of an adjacent cell to Unlocked. If the box is checked, it locks the adjacent cell, and places a formula in that cell based on the row. The problem is that I have the following code in my sheet object 59 times. I was hoping there is a way to do this with an array or something else. The Checkboxes are all labeled as CheckBox1 - CheckBox59. CheckBox1 is in Cell A7, CheckBox 59 is in cell A65.

I did have this consolidated in an earlier version, but used a scripting library to check the value of each cell in a range, then populated the results based on each cells value (True/False). This worked, but took 3-5 seconds to process each time a box was checked/unchecked. If this is the only way, the speed of the new method outweighs the 59 separate subs.

Private Sub CheckBox55_Click()

    Dim C As Range
    
    Set C = Range("C61")
    
    ActiveSheet.Unprotect "password"
    If CheckBox55.Value = False Then
        C.Formula = "=M" & C.Row
        C.FormulaHidden = True
        C.Locked = True
        C.Offset(0, 1).Select
        Else
        If C.Value = "" Or C.Formula = "=M" & C.Row Then
            C.FormulaHidden = False
            C.Locked = False
            C.Value = ""
            C.Select
        End If
    End If
    If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else
        ActiveSheet.Protect "password"
End Sub

 

[SensualCarrots]

Awesome, thanks for the link!

So something I noticed while building my form. I haven't pinned down what causes the problem, but I'm in the error testing phase now. I ran every possible combination of my macros that I can think of, and things work fine. However, while building it, and encountering errors, the checkbox macro would stop working, kind off. It was the workbook open module that gets broken. I'm not sure if certain errors clear the values that the workbook open macro stores in memory, but is there a way to make sure if an error occurs somewhere in the worksheet, that it get reloaded without having to call the sub from every macro in the workbook? If that's what i have to do, it's not a huge deal. I'll try to isolate what causes it, but figured I'd mention it in the meantime.

 

[DanteAmor]

Awesome, thanks for the link!

So something I noticed while building my form. I haven't pinned down what causes the problem, but I'm in the error testing phase now. I ran every possible combination of my macros that I can think of, and things work fine. However, while building it, and encountering errors, the checkbox macro would stop working, kind off. It was the workbook open module that gets broken. I'm not sure if certain errors clear the values that the workbook open macro stores in memory, but is there a way to make sure if an error occurs somewhere in the worksheet, that it get reloaded without having to call the sub from every macro in the workbook? If that's what i have to do, it's not a huge deal. I'll try to isolate what causes it, but figured I'd mention it in the meantime.

You can reload the controls in some other event, for example when you select a sheet or a cell.