Checkbox to Hide/Unhide Columns Based on Value in Cell

[BLarche]

Hey everyone. I am new to this board to bear with me if my question is
not detailed enough. I have several columns of data in my worksheet,
all with column headers ranging from January - Decemeber. I have
created some checkboxes with the names of January - December as well.
I want to create a macro for each checkbox (12 subs) that will
hide/unhide any column with that corresponding name in the header. For
instance, when I check the January checkbox, I want it to hide or
unhide, depending on the state of that column, all columns with the
header of January. All column headings are in row 9, so I will just
need to check row 9 in every column of my worksheet for the
corresponding heading.

Does anyone have any solutions for me?

 

[Die_Another_Day]

Private Sub CheckBox1_Click()
Dim r As Range
Dim cell As Range
Set r = Rows(9)
For Each cell In r.Cells
If cell = CheckBox1.Caption Then
cell.EntireColumn.Hidden = CheckBox1.Value
End If
Next
End Sub

Charles Chickering

 

[BLarche]

I am a little confused. I guess I forgot to state my question clear
enough. You have the right concept, just a little tweaking. My
checkbox names are "Jan", "Feb", etc., while my column headings are
"January", "February", etc.

When you say CheckBox1.Caption, is ".Caption" just the name of the
checkbox that you see? Also, how do you define the CheckBox1,
CheckBox2, etc. values??

Thanks again for your help!

 

[Die_Another_Day]

The CheckBox object has a "Name" Property and a "Caption" property, the
"Name" property is what VB knows, and be default is listed as
CheckBox1, CheckBox2, etc... the "Caption" property is what the user
sees as text to the right of the CheckBox, I assumed that you had set
it to "January" etc... The value is whether or not the CheckBox has a
CheckMark in it, therefore with my code, if the box is checked all
columns are hidden, if cleared then they are visible. If neither your
Caption nor your Name is exactly what you want to match then hard code
the value in like this:
Private Sub Jan_Click() 'Change "Jan" to the name of your "January"
CheckBox
Dim r As Range
Dim cell As Range
Set r = Rows(9)
For Each cell In r.Cells
If cell = "January" Then
cell.EntireColumn.Hidden = Jan.Value
'Change "= Jan.Value" to "= Not(Jan.Value)" to invert logic
End If
Next
End Sub

HTH

Charles Chickering