VBA Code Hide/Unhide columns with Checkboxes

[Marty1]

Hello Everyone,

This is my first time to the forum and newbie to Excel VBA.
I have a userform labels role_selection with 6 checkboxes. Each checkbox with the caption labels as follow: Revenues, Erevenues, Occupancy, ADR, EADR and RevPAR.
These checkboxes refers to a tab where I have all the datas to hide / unhide

My question is how can i write a program to determine which checkbox is selected (true or false) and hide boxes which are unchecked (and unhide the selected boxes).

Many thanks in Advance!

 

[Sunvisor]

I did this before with rows...the check box controls a cell TRUE/FALSE, and you name that range something ...in this case its "ADVC"

Go from here, see what happends.

Application.ScreenUpdating = False
Dim rCell As Range
For Each rCell In ActiveSheet.Range("ADVC").Cells
    rCell.EntireRow.Hidden = Not rCell.Value
    Next
Application.ScreenUpdating = True

 

[Marty1]

Thanks a lot for your answer but I can't get this to work.

I basically need a vba code that I can assign to a command button as follows:

Hide entire columns: AI:AX

If cell BC3 = TRUE, then unhide AJ and AR columns
If cell BC4 = TRUE, then unhide AK and AS columns
If cell BC5 = TRUE, then unhide AL and AT columns
If cell BC6 = TRUE, then unhide AM and AU columns
If cell BC7 = TRUE, then unhide AN and AV columns
If cell BC8 = TRUE, then unhide AO and AW columns

Thanks you very much for your help

 

[Jerry Sullivan]

Hi Marty,

Try this...

Sub Show_Columns()
    Dim i As Long, rng As Range
    Application.ScreenUpdating = False
    
    Columns("AI:AX").Hidden = True
    With Range("AG1")
        For i = 3 To 8
            If Range("BC" & i) Then
                If rng Is Nothing Then
                    Set rng = Union(.Offset(, i), .Offset(, i + 8))
                Else
                    Set rng = Union(rng, .Offset(, i), .Offset(, i + 8))
                End If
            End If
        Next
    End With
    If rng Is Nothing Then Exit Sub
    rng.EntireColumn.Hidden = False
End Sub

 

[mikerickson]

If you use check boxes from the Forms menu, not ActiveX, you could assign them all to this macro. It is controlled by the caption of the check box.

Sub CheckBox_Click()
    Dim otherSheet As Worksheet
    Set otherSheet = ThisWorkbook.Worksheets("sheet2")
    
    If TypeName(Application.Caller) = "String" Then

        With ActiveSheet.Shapes(Application.Caller)

            Select Case LCase(.AlternativeText)
                Case "revenues"
                    otherSheet.Range("AJ1, AR1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
                Case "erevenues"
                    otherSheet.Range("AK1, AS1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
                Case "occupancy"
                    otherSheet.Range("AL1, AT1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
                Case "adr"
                    otherSheet.Range("AM1, AU1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
                Case "eadr"
                    otherSheet.Range("AN1, AV1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
                Case "revpar"
                    otherSheet.Range("AO1, AW1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
            End Select
        End With
    End If
End Sub

 

[Marty1]

Thanks you very much, I got everything to work perfectly with this code, but a ticked checkbox hides the column. Instead I would like an unticked checkbox to hide them:

Sub CheckBox_Click()
Dim otherSheet As Worksheet
Set otherSheet = ThisWorkbook.Worksheets("MonthEnd_Stats")

If TypeName(Application.Caller) = "String" Then

With ActiveSheet.Shapes(Application.Caller)

Select Case LCase(.AlternativeText)
Case "rms"
otherSheet.Range("AI1, AQ1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
Case "revenue"
otherSheet.Range("AJ1, AR1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
Case "occupancy"
otherSheet.Range("AM1, AU1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
Case "adr"
otherSheet.Range("AN1, AV1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
Case "e-adr"
otherSheet.Range("AO1, AW1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
Case "revpar"
otherSheet.Range("AP1, AX1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
Case "e-revenue"
otherSheet.Range("AL1, AT1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
Case "e-rms"
otherSheet.Range("AK1, AS1").EntireColumn.Hidden = (.ControlFormat.Value = xlOn)
End Select
End With
End If
End Sub

Thanks for your help!

 

[mikerickson]

You could either use .ControlFormat.Value <> xlOn or use Not(.ControlFormat.Value = xlOn)

 

[Marty1]

Very good code, thanks a lot for your help!