Excel - VBA Toggle Button-CodePudding

I have a spreadsheet made up and would like the hide and unhide columns on a toggle button but based on a value (of between 1 and 10) within a cell called ("Quotes"). Being new to VBA I have managed to hide ALL the columns on the first toggle but on the second toggle they ALL come back without checking the value within the ("Quotes") cell. How would I be able to check against the value in the ("Quotes") cell and then unhide only the columns needed.

Hope this makes sense and more than willing to share my spreadsheet if needed.

Craig

Edit -

Thanks for the feedback Tom, Hopefully the following will help describe it better ?

I Am currently using the following VBA code which works to hide all the columns I would like to hide, but when toggled again 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

Ideally I would like the unhide fuction to check against a cell called ("Quotes") for a number between 1 and 10 and then action an 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")

CodePudding user response：

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