ToggleButton hide columns under conditions

[TimvMechelen]

Hi all,

I have the code below now, but when I manually hide a column (for example column C) and use the toggle button twice, column C is visible again and I want it to stay hidden. Is there a possibility to do that?

Private Sub ToggleButton1_Click()
Dim xAddress As String
xAddress = "A:Z"
If ToggleButton1.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = True
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = False
End If
End Sub

 

[mikerickson]

It is inside the proceedure (between Sub and End Sub)

 

[MickG]

Another option:-
Sorry about the previous !!!

Option Explicit
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
Private [COLOR="Navy"]Sub[/COLOR] ToggleButton1_Click()
[COLOR="Navy"]Dim[/COLOR] xAddress [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] Range
Static Fd [COLOR="Navy"]As[/COLOR] Boolean
xAddress = "A:Z"
Fd = Fd Xor True
 
 [COLOR="Navy"]If[/COLOR] Fd [COLOR="Navy"]Then[/COLOR]
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Range("A1:Z1")
    [COLOR="Navy"]If[/COLOR] Ac.Columns.Hidden [COLOR="Navy"]Then[/COLOR]
       [COLOR="Navy"]If[/COLOR] rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] rng = Ac Else [COLOR="Navy"]Set[/COLOR] rng = Union(rng, Ac)
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] If
 
[COLOR="Navy"]If[/COLOR] ToggleButton1.Value [COLOR="Navy"]Then[/COLOR]
    Application.ActiveSheet.Columns(xAddress).Hidden = True
[COLOR="Navy"]Else[/COLOR]
    Application.ActiveSheet.Columns(xAddress).Hidden = False
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]If[/COLOR] Not Fd [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        rng.EntireColumn.Hidden = True
        [COLOR="Navy"]Set[/COLOR] rng = Nothing
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[TimvMechelen]

Perhaps

Private Sub ToggleButton1_Click()

Static StayHidden() As Boolean
Dim xRange As Range
Dim i As Long

Set xRange = Range("A:G")

On Error Resume Next
If UBound(StayHidden) < 1 Then
    ReDim StayHidden(1 To xRange.Columns.Count)
End If
On Error GoTo 0

If ToggleButton1.Value Then
    For i = 1 To xRange.Columns.Count
        StayHidden(i) = xRange.Columns(i).Hidden
    Next i
    Rem hide all
    xRange.EntireColumn.Hidden = True
Else
    For i = 1 To xRange.Columns.Count
        xRange.Columns(i).Hidden = StayHidden(i)
    Next i
End If

End Sub

If I hide column G. This code only hides till column G, not column H to Z.

 

[TimvMechelen]

Another option:-
Sorry about the previous !!!

Option Explicit
[COLOR=Navy]Dim[/COLOR] rng [COLOR=Navy]As[/COLOR] Range
Private [COLOR=Navy]Sub[/COLOR] ToggleButton1_Click()
[COLOR=Navy]Dim[/COLOR] xAddress [COLOR=Navy]As[/COLOR] [COLOR=Navy]String,[/COLOR] Ac [COLOR=Navy]As[/COLOR] Range
Static Fd [COLOR=Navy]As[/COLOR] Boolean
xAddress = "A:Z"
Fd = Fd Xor True
 
 [COLOR=Navy]If[/COLOR] Fd [COLOR=Navy]Then[/COLOR]
 [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Ac [COLOR=Navy]In[/COLOR] Range("A1:Z1")
    [COLOR=Navy]If[/COLOR] Ac.Columns.Hidden [COLOR=Navy]Then[/COLOR]
       [COLOR=Navy]If[/COLOR] rng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR] [COLOR=Navy]Set[/COLOR] rng = Ac Else [COLOR=Navy]Set[/COLOR] rng = Union(rng, Ac)
    [COLOR=Navy]End[/COLOR] If
 [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]End[/COLOR] If
 
[COLOR=Navy]If[/COLOR] ToggleButton1.Value [COLOR=Navy]Then[/COLOR]
    Application.ActiveSheet.Columns(xAddress).Hidden = True
[COLOR=Navy]Else[/COLOR]
    Application.ActiveSheet.Columns(xAddress).Hidden = False
[COLOR=Navy]End[/COLOR] If

[COLOR=Navy]If[/COLOR] Not Fd [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]If[/COLOR] Not rng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
        rng.EntireColumn.Hidden = True
        [COLOR=Navy]Set[/COLOR] rng = Nothing
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

This one works, thanks!

Why do you have to place the "Option Explicit" & "Dim rng As Range" before the macro?
This makes it not clear when I have multiple macros on one sheet.

 

[MickG]

You're welcome
You can also do it like this:-

Private [COLOR="Navy"]Sub[/COLOR] ToggleButton1_Click()
[COLOR="Navy"]Dim[/COLOR] xAddress [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] Range
Static rng [COLOR="Navy"]As[/COLOR] Range
Static Fd [COLOR="Navy"]As[/COLOR] Boolean
xAddress = "A:Z"
Fd = Fd Xor True
 
 [COLOR="Navy"]If[/COLOR] Fd [COLOR="Navy"]Then[/COLOR]
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Range("A1:Z1")
    [COLOR="Navy"]If[/COLOR] Ac.Columns.Hidden [COLOR="Navy"]Then[/COLOR]
       [COLOR="Navy"]If[/COLOR] rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] rng = Ac Else [COLOR="Navy"]Set[/COLOR] rng = Union(rng, Ac)
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] If
 
[COLOR="Navy"]If[/COLOR] ToggleButton1.Value [COLOR="Navy"]Then[/COLOR]
    Application.ActiveSheet.Columns(xAddress).Hidden = True
[COLOR="Navy"]Else[/COLOR]
    Application.ActiveSheet.Columns(xAddress).Hidden = False
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]If[/COLOR] Not Fd [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        rng.EntireColumn.Hidden = True
        [COLOR="Navy"]Set[/COLOR] rng = Nothing
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick