Dynamically changing checkboxes within a sheet

[Tails888]

I have the following issue:

I have created an dynamic checklist that allows the user to utilize checkboxes to display the status of each line item within their overall project. However, I want to enable the user to be able to change the layout of the checklist and add/subtract checkboxes when needed. However, in the construction of the VBA code that I used to create the click event I was unable to avoid using the actual object's name in the code:
Private Sub CheckBox399_Click()
'Call Unprotect
xvarb = CheckBox399.Value
If xvarb = True Then
CheckBox399.TopLeftCell.Offset(0, 4).Activate
Call AddDetails
'Call Protect
Else
'Call Unprotect
CheckBox399.TopLeftCell.Offset(0, 4).Activate
Call ClearDetails
'Call Protect
End If
End Sub

Is there a way to allow the code to identify each checkbox dynamically, instead of specifically each box. As I have currently employed the code, I have to replicate the lines above out for each check box that appears on the sheet. There has to be a better way to acheive the same effect, except it is beyond my powers. Any info in the right direction would be of great help.

Thanks
RT

 

[Juan Pablo González]

The following may seem a little complicated, but its not really... just follow me:

In the standard module where you have your current subs (AddDetails and ClearDetails), add a new Public variable like this:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Public</SPAN> Col <SPAN style="color:#00007F">As</SPAN> Collection

<SPAN style="color:#00007F">Sub</SPAN> AddDetails()
    <SPAN style="color:#007F00">'Code here</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ClearDetails()
    <SPAN style="color:#007F00">'Code here</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
Now, insert a new class module, and rename it to clsCheckBox. Put this code in it:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">WithEvents</SPAN> Cb <SPAN style="color:#00007F">As</SPAN> MSForms.CheckBox

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Cb_Change()
    <SPAN style="color:#007F00">'Call Unprotect</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Cb.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Cb.TopLeftCell.Offset(0, 4).Activate
        <SPAN style="color:#00007F">Call</SPAN> AddDetails
        <SPAN style="color:#007F00">'Call Protect</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#007F00">'Call Unprotect</SPAN>
        Cb.TopLeftCell.Offset(0, 4).Activate
        <SPAN style="color:#00007F">Call</SPAN> ClearDetails
        <SPAN style="color:#007F00">'Call Protect</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Now, in the module of the worksheet where all the checkboxes are, put this code:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()
    <SPAN style="color:#00007F">Dim</SPAN> MyClass <SPAN style="color:#00007F">As</SPAN> clsCheckBox
    <SPAN style="color:#00007F">Dim</SPAN> OLEObj <SPAN style="color:#00007F">As</SPAN> OLEObject
    <SPAN style="color:#00007F">Set</SPAN> Col = <SPAN style="color:#00007F">New</SPAN> Collection
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> OLEObj <SPAN style="color:#00007F">In</SPAN> Me.OLEObjects
        <SPAN style="color:#00007F">If</SPAN> TypeName(OLEObj.Object) = "CheckBox" <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> MyClass = <SPAN style="color:#00007F">New</SPAN> clsCheckBox
            <SPAN style="color:#00007F">Set</SPAN> MyClass.Cb = OLEObj.Object
            Col.Add MyClass
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> OLEObj
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Now, that code will run whenever you activate the sheet. It could also be used in the Workbook_Open, but well, for now let's see if this works. Back in Excel, change to another sheet and go back to the sheet with the checkboxes, to force the code to run.

Then, check/uncheck some of the checkboxes to see if it works.

 

[Tails888]

I employed the code that you explained, however it crashes at the line which reads: Dim MyClass As clsCheckBox. It can't find the project or library.
Please advise.

 

[Juan Pablo González]

I hope its only causing an error and not crashing :D !

However, the solution should be simple. When you inserted the class module, did you rename it to clsCheckBox ?

You do so by double clicking the class module to bring up the code pane, and then, in the properties window (Press F4 if you can't see it) you should see the (Name) property, that you can change in there.