Search code examples
excelvbauserformtogglebutton

How to use vba userform to make a toggle feature work for column?

How would you then go about hiding/unhiding columns separately? From Columns C to M. This what I used for Hiding/ Unhiding set rows using toggle buttons. 

Private Sub togbHAZOP_Click()
If togbHAZOP.Value = True Then
    Sheets("Updated Hours EST").Rows("5:26").EntireRow.Hidden = True
Else
    Sheets("Updated Hours EST").Rows("5:26").EntireRow.Hidden = False
End If

End Sub

Solution

  • You have a Boolean property being assigned to opposite Boolean values in opposing branches of a conditional expression involving a Boolean property. In other words:

    If {bool} Then
    foo = True
Else
    foo = False
End If

    There's no need for a conditional.

    Sheets("Updated Hours EST").Rows("5:26").Hidden = togbHAZOP.Value

    To toggle column visibility, use .Columns instead of .Rows, and a range address that represents columns instead of rows.

    Sheets("Updated Hours EST").Columns("C:M").Hidden = togbHAZOP.Value

    If you want them "separate", then make them separate..

    Sheets("Updated Hours EST").Columns("C:C").Hidden = toggleForColumnC.Value
Sheets("Updated Hours EST").Columns("D:D").Hidden = toggleForColumnD.Value
Sheets("Updated Hours EST").Columns("E:E").Hidden = toggleForColumnE.Value
...