VBA to dynamic toggle columns hide/unhide

[Celia9]

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(xAddress).Hidden = True
Else
Application.ActiveSheet.Columns(xAddress).Hidden = False
End If
End Sub

But instead of F:G I want to select the columns based on a part of a name of a header in my table.

For example I have a table with these header names;

person1 - M1

person2 - M1

person3 - M2

I want to hide/unhide columns based upon the - M1 part of the headername.

Anyone knows a solution?

[#2]

@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

[#3]

Hi Hans,

This works, but I have multiple columns with M1, and now it hides only the first column. Do you know why?

[#4]

@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

[#5]

You are amazing Hans!

You also happen to know the solution to open and close anything except M1?
Something with <> * -M1 I suppose?

[#6]

@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

[#7]

I have 5 groups M1, M2, M3, M4 and M5
I would like to hide all, except M1.

Is that what you wanted this code to do? I get runtime error 1004
Application-defined or object-defined error

[#8]

@Celia9 

The old code toggles the visibility of columns with M1 on and off.

What exactly should the new code do? Hide M2 - M5 and show M1? Or toggle - if so, what?

[#9]

Yes, hide M2 - M5 and show M1

[#10]

@Celia9 

Perhaps this?

Private Sub ShowOnlyM1()
    Const HeaderRow = 1
    Dim rng As Range
    Dim adr As String
    Cells.EntireColumn.Hidden = True
    Set rng = Rows(HeaderRow).Find(What:="* - M1", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            rng.EntireColumn.Hidden = False
            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
    Range("A1").Select
End Sub

[#11]

Thank you for helping me, Hans!

I get: Unable to set the hidden property of the Range class.

This row is yellow
Cells.EntireColumn.Hidden = True

I tried to change
Range("A1").Select
To A4, because that is where my table begins (I didn't know if that would matter)
But it doesn't obviously

[#12]

@Celia9 

The code works for me. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#13]

Sure, here it is!
https://www.dropbox.com/scl/fi/67r1ud35dkez5nazhal48/Planninglist-buttons.xlsm?dl=0&rlkey=hza0srmr05...

Thank you!

[#14]

@Celia9 

Thanks, that helped. See the attached version. I added 5 command buttons.

[#15]

This is amazing!!!

I cleaned up your version, I also have M6, so when I filter, I do not see the last 3 columns anymore (workorder, description ann location)

Where in the code can I find this option?

And also, how can I thank you for all your help? Is there something I can sponsor or something like that?

[#16]

@Celia9 

The line

    Range("C1").Resize(1, LastColumn - 5).EntireColumn.Hidden = True

hides all columns in the used range except for the first two and the last three. In the sample workbook, the data extend from column A to column R, so columns C to O get hidden, while columns A, B, P, Q and R will always remain visible.

[#17]

Thank you so much!