Setting CheckBox value based on state of column (hidden/unhidden)

Hello Jason,

Welcome to the Forum!


You should post your workbook. It will be much easier to answer your questions and provide solutions.

Something like this might work.

It might not be worth the effort with only 5 columns, but a custom Class of checkboxes would be another way to approach this, rather than seperate, identical event code for each checkbox.

By creating a custom Class, one can make a custom Object and manipulate it like the native VBA objects.

This code will create a custom object, called a clsCheckColumn.
clsCheckColumn objects have three properties: .ChkBox (a check box), .Column (a range) and .HideWhenValueIs (a boolean value)

When the user clicks on a checkbox that is the .ChkBox property of a clsCheckColumn, the .Column of that clsCheckColumn will be hidden or unhidden depending on the value of the check box and the value of its .HideWhenValueIs property.

To create this class, I inserted a class module and used the Property Window to give that module the name clsCheckColumn. And I put this code in the class module.

Please Login or Register  to view this content.

The three variables declared at the top, ChekBox, myCol and ShowWhenValueIs are paired with the three properties ChkBox, Column and HideWhenValueIs.

The "WithEvents" in the declaration of ChekBox is what connects the clicking of the ChekBox to the Sub ChekBox_Click() routine, which hides or unhides myCol.

The three properties, ChkBox, Column and HideWhenValueIs are controled by the proprerty Get Let/Set routines (Let if the properety is a data type (e.g. Boolean, Integer,...), Set if it's an object (e.g. checkbox, range))

The property Let/Set routines call the sub SyncFromSheet to set the value of the ChekBox to the proper value. i.e. make the checkbox value match the hidden/unhidden status of the column.

The class is used by putting this in the Userform's code module

Please Login or Register  to view this content.

The LinkedBoxes collection (which persists as long as the userform is loaded) will hold the clsCheckColumn objects.

The meat of the Initialize event is the loop that
1) creates a new instance of clsCheckColumn, with the variable LinkedBox.
2) assigns the properties of that new clsCheckColumn, i.e. associates which check box with which column
3) puts it into the LinkedBoxes collection
4) clears the variable LinkedBox for the next loop

Once a checkbox/column has been paired by being properties of the same clsCheckColumn, nothing more needs to be done. By being properties of that class, when a checkbox is clicked, its column will be hidden/unhidden.

Using a custom class like this does not prevent you from having seperate, normal style event prodeedures for the checkboxes used as properties of the custom class.

The machinery for the Property statements looks 1) trivial 2)long, but it does allow the class to "intervene" in the assignment of properties.
For example, the Column property takes the argument and assigns myCol to the column defined by the upper-left cell of the property, preventing a multi-column myCol (this can be changed if you want.)
It also calls the SyncWithSheet routine everytime that a property is changed, insuring that the checkbox matches the proper value for the hiddenness of the column.

I could have used this as the code in the class clsCheckColumn, avoiding all the Property routines:

Please Login or Register  to view this content.

But that would have required an intialize loop like

Please Login or Register  to view this content.

Note the explicit call of .SyncFromSheet (and the making it public in the class module). Also, if the value of a clsCheckColumn is changed in the middle its life, the checkbox might not have the proper value. SyncFromSheet will have to be explicitly called. I prefer to not to have to handle internal matters (like keeping things in sync) from the outside of a custom class.

In either formulation, it is easier to write (and alter) one event routine in a class module than the 20 event routines in a userform's module that you are looking at.