Forum Discussion
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...
Hi Hans,
This works, but I have multiple columns with M1, and now it hides only the first column. Do you know why?
This works, but I have multiple columns with M1, and now it hides only the first column. Do you know why?
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
- You are amazing Hans!
You also happen to know the solution to open and close anything except M1?
Something with <> * -M1 I suppose?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- 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