Forum Discussion

Celia9's avatar
Celia9
Brass Contributor
Jul 07, 2022

VBA to dynamic toggle columns hide/unhide

Hi,   I have a VBA to toggle my columns hide/unhide Private Sub ToggleButton1_Click() Dim xAddress As String xAddress = "F:G" If ToggleButton1.Value Then Application.ActiveSheet.Columns(xAdd...
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Jul 07, 2022

Celia9 

For example

Private Sub ToggleButton1_Click()
    Const HeaderRow = 1
    Dim rng As Range
    Set rng = Rows(HeaderRow).Find(What:="* - M1", LookAt:=xlWhole)
    If rng Is Nothing Then
        MsgBox "M1 not found!", vbExclamation
        Exit Sub
    End If
    rng.EntireColumn.Hidden = ToggleButton1.Value
End Sub
Celia9's avatar
Celia9
Brass Contributor
Jul 08, 2022
Hi Hans,

This works, but I have multiple columns with M1, and now it hides only the first column. Do you know why?
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 08, 2022

    Celia9 

    The code that I posted looks only for the first instance. Here is a version that should handle all instances:

    Private Sub ToggleButton1_Click()
    Const HeaderRow = 1
    Dim rng As Range
    Dim adr As String
    Set rng = Rows(HeaderRow).Find(What:="* - M1", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            rng.EntireColumn.Hidden = ToggleButton1.Value
            Set rng = Rows(HeaderRow).Find(What:="* - M1", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
End Sub

     

    • Celia9's avatar
      Celia9
      Brass Contributor
      Jul 11, 2022
      You are amazing Hans!

      You also happen to know the solution to open and close anything except M1?
      Something with <> * -M1 I suppose?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 11, 2022

        Celia9 

        Do you want the toggle button to toggle the visibility of all columns based on whether their header ends in "- M1"? If so, try this:

        Private Sub ToggleButton1_Click()
    Const HeaderRow = 1
    Dim rng As Range
    Dim adr As String
    Cells.EntireColumn.Hidden = Not ToggleButton1.Value
    Set rng = Rows(HeaderRow).Find(What:="* - M1", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            rng.EntireColumn.Hidden = ToggleButton1.Value
            Set rng = Rows(HeaderRow).Find(What:="* - M1", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
End Sub

Resources