Case Statement for Multiple Checkboxes Hiding/Unhiding Columns

[cmutschler]

Hi!

I'm new to VBA and need some help changing this to a case statement. Thank you for your help!!! Cheryl


If ActiveSheet.Shapes("Check Box 110").ControlFormat.Value = 1 Then
Sheets("DHMO Copay").Columns("C").EntireColumn.Hidden = False
Else: Sheets("DHMO Copay").Columns("C").EntireColumn.Hidden = True
End If
If ActiveSheet.Shapes("Check Box 111").ControlFormat.Value = 1 Then
Sheets("DHMO Copay").Columns("D").EntireColumn.Hidden = False
Else: Sheets("DHMO Copay").Columns("D").EntireColumn.Hidden = True
End If
If ActiveSheet.Shapes("Check Box 122").ControlFormat.Value = 1 Then
Sheets("DHMO Copay").Columns("E").EntireColumn.Hidden = False
Else: Sheets("DHMO Copay").Columns("E").EntireColumn.Hidden = True
End If
If ActiveSheet.Shapes("Check Box 123").ControlFormat.Value = 1 Then
Sheets("DHMO Copay").Columns("F").EntireColumn.Hidden = False
Else: Sheets("DHMO Copay").Columns("F").EntireColumn.Hidden = True
End If

..... a bazillion more checkboxes

 

[JackDanIce]

This loop may help reduce the amount of code, highlighted parts should be all you need to edit:

    Dim x     As Long
    Dim arr() As Variant

    'Check box numbers
    arr = Array(110, 111, 122, 123)
  
    Application.ScreenUpdating = False
    
    With Sheets("DHMO Copay")
        .Cells(1, 3).Resize(, Ubound(arr) + 1).EntireColumn.Hidden = True
        For x = LBound(arr) To UBound(arr)
            If .Shapes("Check Box " & CStr(arr(x))).ControlFormat.Value Then .Cells(1, x + 3).EntireColumn.Hidden = False
        Next x
    End With
    
    Application.ScreenUpdating = True
    Erase arr

It assumes columns to be hidden always starts at column C and continues incrementally, e.g. C, D, E, F, G, etc.

 

[cmutschler]

Wow. Thank you very much!

Giving this a go, any checkbox that I check unhides all columns.

 

[JackDanIce]

Try:

    Dim x     As Long
    Dim arr() As Variant
  
    arr = Array(110, 111, 122, 123)
  
    Application.ScreenUpdating = False
    
    With Sheets("DHMO Copay")
        .Cells(1, 3).Resize(, UBound(arr) + 1).EntireColumn.Hidden = True
        For x = LBound(arr) To UBound(arr)
            If .Shapes("Check Box " & CStr(arr(x))).ControlFormat.Value = 1 Then
                .Cells(1, x + 3).EntireColumn.Hidden = False
            End If
        Next x
    End With
    
    Application.ScreenUpdating = True
    Erase arr

 

[cmutschler]

Man! Perfect!!! Thank you, JackDanIce!!

 

[JackDanIce]

You're welcome