Toggle to hide all columns but unhide based on cell value

user_18917553 0
Viewed 166

I would like the hide and unhide columns with a toggle button, based on a value (between 1 and 10) within a cell called ("Quotes").

I managed to hide ALL the columns on the first click but on the second click they ALL come back without checking the value within the ("Quotes") cell.

How would I check against the value in the ("Quotes") cell and then unhide only the columns needed.

The following VBA code hides the columns I would like to hide, but when toggled brings them all back.

Private Sub ShowHideTemplate_Click()
If ShowHideTemplate.Value Then
    Worksheets("Sheet1").Range("L:M,Q:R,V:W,AA:AB,AF:AG,AK:AL,AP:AQ,AU:A,AZ:BA,BE:BG,BJ:BL").EntireColumn.Hidden = False
    ShowHideTemplate.Caption = "Full"

Else
    Worksheets("Sheet1").Range("L:M,Q:R,V:W,AA:AB,AF:AG,AK:AL,AP:AQ,AU:AV,AZ:BA,BE:BG,BJ:BL").EntireColumn.Hidden = True
    ShowHideTemplate.Caption = "Hidden"

End If

End Sub

I would like the unhide function to check against a cell called ("Quotes") for a number between 1 and 10 and then unhide the columns based on that number.

I.e.

if ("Quotes") = "1" then only unhide ("L:M")
if ("Quotes") = "2" then unhide ("L:M,Q:R,BJ:BL")
if ("Quotes") = "3" then unhide ("L:M,Q:R,V:M,BJ:BL")
if ("Quotes") = "4" then unhide ("L:M,Q:R,V:M,AA:AB,BJ:BL")
if ("Quotes") = "5" then unhide ("L:M,Q:R,V:M,AA:AB,AF:AG,BJ:BL")
if ("Quotes") = "6" then unhide ("L:M,Q:R,V:M,AA:AB,AF:AG,AK:AL,BJ:BL")
if ("Quotes") = "7" then unhide ("L:M,Q:R,V:M,AA:AB,AF:AG,AK:AL,AP:AQ,BJ:BL")
if ("Quotes") = "8" then unhide ("L:M,Q:R,V:M,AA:AB,AF:AG,AK:AL,AP:AQ,AU:AV,BJ:BL")
if ("Quotes") = "9" then unhide ("L:M,Q:R,V:M,AA:AB,AF:AG,AK:AL,AP:AQ,AU:AV,AZ:BA,BJ:BL")
if ("Quotes") = "10" then unhide (ALL) ("L:M,Q:R,V:M,AA:AB,AF:AG,AK:AL,AP:AQ,AU:AV,AZ:BA,BJ:BL")
togglebutton vba excel
1 Answers

You could use a Select Case Range("Quotes").Value statement, where "Quotes" is a named range. E.g.:

Private Sub ShowHideTemplate_Click()
If ShowHideTemplate.Value Then
    
    Select Case Range("Quotes").Value
        Case Is = 1
            Worksheets("Sheet1").Range("L:M").EntireColumn.Hidden = False
        Case Is = 2
            Worksheets("Sheet1").Range("L:M,Q:R,BJ:BL").EntireColumn.Hidden = False
        Case Is = 3
            Worksheets("Sheet1").Range("L:M,Q:R,V:M,BJ:BL").EntireColumn.Hidden = False
        ' and so on ...
    End Select
    
    'Worksheets("Sheet1").Range("L:M,Q:R,V:W,AA:AB,AF:AG,AK:AL,AP:AQ,AU:A,AZ:BA,BE:BG,BJ:BL").EntireColumn.Hidden = False
    'Modify as you wish:
    ShowHideTemplate.Caption = "Full"

Else
    Worksheets("Sheet1").Range("L:M,Q:R,V:W,AA:AB,AF:AG,AK:AL,AP:AQ,AU:AV,AZ:BA,BE:BG,BJ:BL").EntireColumn.Hidden = True
    ShowHideTemplate.Caption = "Hidden"

End If
End Sub
user_22927220
Related