Consolidate checkbox macros

SensualCarrots

New Member
Joined
Mar 21, 2015
Messages
46
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.

Code:
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
 
Last edited:
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top