Make vba code work for all boxes

Question

Hello so what i want to do is make this code work for all Check Box's 1-50 I want the code to only effect the box that is clicked.

Private Sub CheckBox1_Click()
If MsgBox("Do you want to lock this box?", vbYesNo, "Warning") = vbYes Then
  ActiveSheet.CheckBox2.Enabled = False
Else
End If
End Sub

Answer 1

I see several options (none of which are pretty since this is VBA).

• Option 1: generate the code for all of your check boxes. This is probably the most maintainable. You would first choose reasonable names for all your check boxes (you can assign them by selecting them in Excel and renaming in the top left corner, or run code which will do this for you if you already have a lot of check boxes. This may be useful).

  You can then generate the code and have each one of your subprocedues as follows:

  'example code for one checkbox
  Private Sub chkBox_1_Click()
      Call lockMeUp(Sheet1.chkBox_1.Object)
  End Sub
  

  After you're done with all your code for each checkbox, you could have your lockMeUp subprocedure as follows:

  Sub lockMeUp(chkBox as Object)
      If MsgBox("Do you want to lock this box?", vbYesNo, "Warning") = vbYes Then
          chkBox.Enabled = False
      End If
  End Sub
  

• Option 2: Keep track of all your checked/unchecked statuses through either an Array or a "Settings" hidden sheet, and watch out for that triggered event. You could fire off based off of a sheet's Changed event, and match the row number to your CheckBox number so that you can go off of the Target's row number.

Other options I can think of become more convoluted... I'd be interested to see what other suggestions people have. Thanks!

EDIT You can use some code to refer to a single function as in my example, in conjunction with brettdj's example to get your optimal solution. Bam!

Answer 2

The easy way is to write a class module that will apply one code routine to a collection of Checkboxes

Assuming yu want to run this on all ActiveX checkboxes on the ActiveSheet, then borrowing heavily from Bob Phillip's code from VBAX

1. Insert a Class Module named clsActiveXEvents

   Option Explicit

   Public WithEvents mCheckboxes As MSForms.CheckBox
   
   Private Sub mCheckboxes_Click()
     mCheckboxes.Enabled = (MsgBox("Do you want to lock this box?", vbYesNo, "Warning") = vbNo)
   End Sub
   

2. In a normal module use this code

   Dim mcolEvents As Collection
   
   Sub Test()
   Dim cCBEvents As clsActiveXEvents
   Dim shp As Shape
   
   Set mcolEvents = New Collection
   
   For Each shp In ActiveSheet.Shapes
       If shp.Type = msoOLEControlObject Then
           If TypeName(shp.OLEFormat.Object.Object) = "CheckBox" Then
              Set cCBEvents = New clsActiveXEvents
              Set cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object
           mcolEvents.Add cCBEvents
       End If
   End If
   Next
   End Sub
   

Answer 3

In case you do not know, all Form Controls are treated as Shapes in a Worksheet.

I have a solution that you need to create a new Module, copy-paste in code below and then from Immediate window to the same module. With some assumptions:

• All Check Box Objects are named "Check Box #" where # is a number
• No macro named ResetCheckBoxes() in any other modules of the workbook
• No macro named CheckBox#_Click() in any other modules of the workbook

Run this ResetCheckBoxes once to enable check boxes and Assign a macro to it for you, with relevant generated codes in the immediate window (you might want to put a pause in the loop every 25 check boxes as line buffer in it are limited).

Sub ResetCheckBoxes()
    Dim oWS As Worksheet, oSh As Shape, sTmp As String

    Set oWS = ThisWorkbook.ActiveSheet
    For Each oSh In oWS.Shapes
        With oSh
            If .Type = msoFormControl Then
                If InStr(1, .Name, "Check Box", vbTextCompare) = 1 Then
                    .ControlFormat.Enabled = True
                    sTmp = "CheckBox" & Replace(oSh.Name, "Check Box ", "") & "_Click"
                    .OnAction = sTmp
                    Debug.Print "Sub " & sTmp & "()"
                    Debug.Print vbTab & "ActiveSheet.Shapes(""" & .Name & """).ControlFormat.Enabled = False"
                    Debug.Print "End Sub" & vbCrLf
                End If
            End If
        End With
    Next
End Sub

Example Immediate window output (2 test check boxes):

Happy New Year mate!

Answer 4

To build on the solution offered by @brettdj, since he is specifying ActiveX Controls, I would suggest the following in the Standard Module:

Dim mcolEvents As Collection
Sub Test()
Dim cCBEvents As clsActiveXEvents
Dim o As OLEObject

Set mcolEvents = New Collection

For Each o In ActiveSheet.OLEObjects
    If TypeName(o.Object) = "CheckBox" Then
        Set cCBEvents = New clsActiveXEvents
        Set cCBEvents.mCheckboxes = o.Object
        mcolEvents.Add cCBEvents, o.Name
    End If
Next
End Sub

The differences are:

1. I use the OLEObjects Collection because it is more direct and doesn't waste time on non-OLE shapes.
2. I use TypeName instead of (the mysterious) TypeOf operator because (apparently) the later does not discriminate between OptionButton and CheckBox.
3. I register the Object Name as Key in the Collection to allow for efficient indexing if required.

EDIT: I should have followed the link provided by @brettdj before posting. My solution is using the same principles as are outlined there. Hopefully, its convenient to have it documented here as well?