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

 

[My Aswer Is This]

It would be a lot easier for you if some how the script could have some if statements.
This way we could write one script that would work for all 59 CheckBoxes

You said each one is unique meaning each script is different but you did not say how they are different.

Like if we could use active cell instead of 59 different ranges.

Then we could write one script which could apply to all ranges

For example if active cell address=$C$1 then

We write one script and then in each Checkbox we just enter: Call_Master

Then each checkbox would run a script named Call_Master

Please explain how each script is Unique other then the range

 

[DanteAmor]

We will control all checkboxes from a Class Module. Delete all your code regarding checkboxes.

Put the following in ThisWorkbook events:

Option Explicit
Dim colCheck As Collection 'To the top of all the code
Private Sub Workbook_Open()
    Dim ctl As OLEObject, clsObject As [B][COLOR=#0000ff]Class1[/COLOR][/B]
    
    Set colCheck = New Collection
    For Each ctl In ActiveSheet.OLEObjects
        If Left(ctl.Name, 8) = "CheckBox" Then
            Set clsObject = New [B][COLOR=#0000ff]Class1[/COLOR][/B]
            Set clsObject.chkB = ctl.Object
            colCheck.Add clsObject
        End If
    Next ctl
End Sub

---------------------------------------
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will openthe Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up.

---------------------------------------

Code within class module Class1:

Public WithEvents chkB As MSForms.CheckBox    'Custom checkbox
Private Sub chkB_Click()
  Dim wRow As Long, c As Range
  wRow = Val(Mid(chkB.Name, 9)) + 6
  ActiveSheet.Unprotect "password"
  Set c = Range("C" & wRow)
  If chkB.Value = False Then
    c.Formula = "=M" & c.Row
    c.Offset(0, 1).Select
  ElseIf c.Value = "" Or c.Formula = "=M" & c.Row Then
    c.Value = ""
    c.Select
  End If
  c.FormulaHidden = Not (chkB.Value)
  c.Locked = Not (chkB.Value)
  ActiveSheet.Protect "password"
  If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else ActiveSheet.Protect "password"
End Sub

---------------------------------------
INSERT A CLASS MODULE
Press Alt-F11 to open the VBA editor. From the menu select Insert > CLASS Module. On the sheet that opens, paste the code previous.
---------------------------------------

Run the Open event macro

---------------------------------------
Ready, click on any Checkbox and it will perform the activity in its corresponding cell.

 

[SensualCarrots]

I modified a few things in the class module. There was a duplicate protect worksheet before checking D28, and I added the hidden and locked properties to the false statement. If I could only understand how the workbook module works lol. Thanks again!

Public WithEvents chkB As MSForms.CheckBox    'Custom checkboxPrivate Sub chkB_Click()
  Dim wRow As Long, c As Range
  wRow = Val(Mid(chkB.Name, 9)) + 6
  ActiveSheet.Unprotect "pswrd"
  Set c = Range("C" & wRow)
  If chkB.Value = False Then
    c.Formula = "=M" & c.Row
    c.Offset(0, 1).Select
    c.FormulaHidden = Not (chkB.Value)
    c.Locked = Not (chkB.Value)
  ElseIf c.Value = "" Or c.Formula = "=M" & c.Row Then
    c.Value = ""
    c.Select
    c.FormulaHidden = Not (chkB.Value)
    c.Locked = Not (chkB.Value)
  End If
  If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else ActiveSheet.Protect "pswrd"
End Sub

 

[DanteAmor]

I modified a few things in the class module. There was a duplicate protect worksheet before checking D28, and I added the hidden and locked properties to the false statement. If I could only understand how the workbook module works lol. Thanks again!

Could you do the test?


What do you need to know about the open event?
Just put the code in Thisworkbook and press F5 to execute the code.
With that you can go to the sheet and press any chceckbox.

 

[SensualCarrots]

It works perfectly with my code. What I changed in your was the protecting sheet part. You had...

  ActiveSheet.Protect "password"
  If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else ActiveSheet.Protect "password"

The first activesheet.protect should not have been there that's all. And the hidden and locked feature of the cells need to be applied on true and false conditions of the checkboxes.

When I said I just need to understand how it works, I mean literally learn to understand it so I can reapply it in different situations. I like learning this stuff, but just google the crap out of things until it makes sense. The class module is something i've never seen before, but i like it!

So to recap, after a few minor adjustments, your code works perfectly!

 

[DanteAmor]

It works perfectly with my code. What I changed in your was the protecting sheet part. You had...
The first activesheet.protect should not have been there that's all. And the hidden and locked feature of the cells need to be applied on true and false conditions of the checkboxes.
When I said I just need to understand how it works, I mean literally learn to understand it so I can reapply it in different situations. I like learning this stuff, but just google the crap out of things until it makes sense. The class module is something i've never seen before, but i like it!

Recap:

Your Code

If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else
        ActiveSheet.Protect "password"

My code:

If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else ActiveSheet.Protect "password"

Is the same
----------------------------------------------------------------------------------------------------

  If chkB.Value = False Then
    c.Formula = "=M" & c.Row
    c.Offset(0, 1).Select
[COLOR=#ff0000]    c.FormulaHidden = Not (chkB.Value)[/COLOR]
[COLOR=#ff0000]    c.Locked = Not (chkB.Value)[/COLOR]
  ElseIf c.Value = "" Or c.Formula = "=M" & c.Row Then
    c.Value = ""
    c.Select
[COLOR=#ff0000]    c.FormulaHidden = Not (chkB.Value)[/COLOR]
[COLOR=#ff0000]    c.Locked = Not (chkB.Value)[/COLOR]
  End If

The lines in red are the same, they can go outside the IF-End If and it works

  If chkB.Value = False Then
    c.Formula = "=M" & c.Row
    c.Offset(0, 1).Select
  ElseIf c.Value = "" Or c.Formula = "=M" & c.Row Then
    c.Value = ""
    c.Select
  End If
[COLOR=#FF0000]   c.FormulaHidden = Not (chkB.Value)[/COLOR]
[COLOR=#FF0000]   c.Locked = Not (chkB.Value)[/COLOR]

----------------------------------------------------------------------------------------------------

How does it work

Private Sub Workbook_Open()
    Dim ctl As OLEObject, clsObject As Class1
    
    Set colCheck = New Collection

[COLOR=#008000]'Check all the objects on the sheet[/COLOR]
    For Each ctl In ActiveSheet.OLEObjects
 

       If Left(ctl.Name, 8) = "CheckBox" Then
[COLOR=#008000]'Those who are checkbox "add" them to the class

[/COLOR]
            Set clsObject = New Class1
            Set clsObject.chkB = ctl.Object

[COLOR=#008000]'In this case, the checkboxes are stored in memory in a data collection, for when you click, the Click event in the class is activated.[/COLOR]
            colCheck.Add clsObject
        End If
    Next ctl
End Sub

Code within class module Class1:
This is the complement in the Class, the events of the checkboxes are declared as public,
that way you can execute any event of the checkbox and the Class controls it.

[COLOR=#0000ff]Public WithEvents[/COLOR] chkB As MSForms.CheckBox    'Custom checkbox
[COLOR=#008000]'In this example the Click event.[/COLOR]
Private Sub chkB_Click()

 

[SensualCarrots]

Your Code

If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else
        ActiveSheet.Protect "password"

My code:

If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else ActiveSheet.Protect "password"

Is the same

I agree, however in your original post you had:

Public WithEvents chkB As MSForms.CheckBox    'Custom checkboxPrivate Sub chkB_Click()
  Dim wRow As Long, c As Range
  wRow = Val(Mid(chkB.Name, 9)) + 6
  ActiveSheet.Unprotect "password"
  Set c = Range("C" & wRow)
  If chkB.Value = False Then
    c.Formula = "=M" & c.Row
    c.Offset(0, 1).Select
  ElseIf c.Value = "" Or c.Formula = "=M" & c.Row Then
    c.Value = ""
    c.Select
  End If
  c.FormulaHidden = Not (chkB.Value)
  c.Locked = Not (chkB.Value)
[COLOR=#ff0000]  ActiveSheet.Protect "password"[/COLOR]
  If ActiveSheet.Range("D28") = "Unlock" Then Exit Sub Else ActiveSheet.Protect "password"

End Sub

The red line was the problem. There was a double protect, and the first one was not contained within an If statement. Therefore, the sheet would lock regardless of whether or not D28's value was "Unlock". I simply removed that line.

The lines in red are the same, they can go outside the IF-End If and it works

  If chkB.Value = False Then
    c.Formula = "=M" & c.Row
    c.Offset(0, 1).Select
  ElseIf c.Value = "" Or c.Formula = "=M" & c.Row Then
    c.Value = ""
    c.Select
  End If
[COLOR=#ff0000]  c.FormulaHidden = Not (chkB.Value)[/COLOR]
[COLOR=#ff0000]  c.Locked = Not (chkB.Value)[/COLOR]

I agree completely. I'm not sure what happened when I copied it, but something wasn't working and i started tinkering. I am going to go back and look at this again.

Thanks for the explanation on the Workbook_Open portion. I have never seen OLEObject and Class1 before, so I need to research and understand those before really trying to comprehend. I know VBA is kind of old and outdated (according to friends and family who are in the software engineering industry), but I really enjoy working with this stuff and coming up with new ideas. Are there any good resources, free or paid, that you would recommend? Every time I try something new, I struggle and end up learning something new, but it makes for very slow production. I'd rather just take the time and learn it than trial and error.

 

[DanteAmor]

From my perspective.

The red line was the problem. There was a double protect, and the first one was not contained within an If statement.
Aaaaah, That line, I forgot to remove it after my tests.

Thanks for the explanation on the Workbook_Open portion. I have never seen OLEObject and Class1 before,
There are many new things in excel for everyone, for me, even for other experts in this forum.

I know VBA is kind of old and outdated .
Old yes, but if it were outdated, thousands of people in the world would not use it.

Are there any good resources, free or paid, that you would recommend?
In this forum they put resource announcements, for example:
https://www.mrexcel.com/products/excel-custom-functions-straight-to-the-point/

trial and error.
There is no other way to learn.

 

[DanteAmor]

I remembered an example to understand a little about classes and objects, check the following:

https://www.businessprogrammer.com/vba-class-module-example/