ActiveX Check Boxes: hide/unhide columns when 1 or more boxes are checked

[jrenee]

Hello,

I have 3 ActiveX Check Boxes named "Red," "Green," and "Blue" that will unhide and hide columns when the value in a row meets the criteria identified in the code. When 1 box is checked and unchecked, the result is good (check the box and the correct columns are unhidden, uncheck the box and the columns are hidden again).

I would also like the option of checking multiple boxes to display different column combinations. For example: Check both "Red" and "Blue" displays both red and blue columns. Unckeck "Blue" and red columns remain displayed.

Problem: when I check more than 1 box, the columns that were revealed with the first check box become hidden again.

Here is a sample of the code I'm using... I'm new to VBA...thank you for your help!

Private Sub CheckBox1_Click()
'
' CheckBox1 Macro: Unhides all columns with the Word "Red" in row 11.
'
BeginColumn = 8
EndColumn = 16
ChkRow = 11

Application.ScreenUpdating = False
For ColCnt = BeginColumn To EndColumn
    If CheckBox1.Value = True And Cells(ChkRow, ColCnt).Value = "Red" Then
        Cells(ChkRow, ColCnt).EntireColumn.Hidden = False
    Else
        Cells(ChkRow, ColCnt).EntireColumn.Hidden = True
    End If
Next ColCnt
            
End Sub

 

[Warship]

Call a common routine, something like...

Private Sub CheckBox1_Click()
    HideUnhideColumns
End Sub

Private Sub CheckBox2_Click()
    HideUnhideColumns
End Sub

Private Sub CheckBox3_Click()
    HideUnhideColumns
End Sub

Private Sub HideUnhideColumns()
    BeginColumn = 8
    EndColumn = 16
    ChkRow = 11
    Application.ScreenUpdating = False
    For ColCnt = BeginColumn To EndColumn
        Cells(ChkRow, ColCnt).EntireColumn.Hidden = True
        If CheckBox1.Value = True And Cells(ChkRow, ColCnt).Value = "Red" Then
            Cells(ChkRow, ColCnt).EntireColumn.Hidden = False
        End If
        If CheckBox2.Value = True And Cells(ChkRow, ColCnt).Value = "Green" Then
            Cells(ChkRow, ColCnt).EntireColumn.Hidden = False
        End If
        If CheckBox3.Value = True And Cells(ChkRow, ColCnt).Value = "Blue" Then
            Cells(ChkRow, ColCnt).EntireColumn.Hidden = False
        End If
    Next ColCnt
End Sub

Also note that the above will be matching the colors case sensitive, ie "Red" does not equal "red".
To avoid case sensitivity, change the three "color" lines to:

If CheckBox1.Value = True And LCase(Cells(ChkRow, ColCnt).Value) = "red" Then

 

[jrenee]

I applied your code to my file and it works perfectly! Thank you for your help and quick response!