Toggle for Hiding & Unhiding Columns with dynamic Macro Button

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

print
This is about macro I have used quite often in the last few years that achieves two features:

a) toggles to hide or unhide columns set by the user and

b) the macro button changes in font colour and text wording to confirm to the user the change made. 

In range A1:J1 there are labels saying Show or Hide.  You can make this a Range Name instead maybe “ViewSettings”.  These labels can be a lighter grey so they are not distracting and unprotected so the user can configure which columns to show or hide.  They could be totally invisible if formatted to white but then you might forget the range needs these labels and delete them.   If having those labels there bugs you then show them on another sheet say Sheet2 and adjust the code to check Sheet2’s show or hide label and act upon the Sheet1.

There is one standard forms button in this case that will change in font colour and words.  I like this what I am calling a dynamic button part a lot because it triggers the macro, tells the user the result and reflects the current state achieved which is an efficient use of worksheet real estate.  You need to get the name of the button you create by selecting it and going into the Name Box small window top LHS before the Formula Bar.  OK some might say that button is very 90s looking but hey the 90s were great and it’s simpler to code with than a 3D shape with all it’s miriad of effects.  In this forms macro button you can only change the font and the text, but you can use a shape if you like but the colour and formatting changes will be more complex to code and I am not sure they are better looking anyway. 

Sub ToggleColsMarkedHidden()
'---------------------------------------------------------------------------------------
' Procedure : ToggleColsMarkedHidden
' Author    : John Hackwood theexcelfactor.com e:[email protected]
' Date      : 10 01 2018
' Purpose   : Macro checks View setting of column in range for
'             columns maked hidden and toggles hiding /unhiding'
'---------------------------------------------------------------------------------------
'
    Dim c As Range
  
    For Each c In Sheet1.Range("a1:J1")

        If c.Value2 = "Hide" Then
            If c.EntireColumn.Hidden = False Then
                c.EntireColumn.Hidden = True
                With ActiveSheet.Buttons("Button 1")
                    If .Font.ColorIndex <> 16 Then
                        .Font.ColorIndex = 16
                        .Characters.Text = "Toggle Hidden Columns - Now HIDDEN"
                    End If
                End With

            Else: c.EntireColumn.Hidden = False
                With ActiveSheet.Buttons("Button 1")
                    If .Font.ColorIndex <> 10 Then
                        .Font.ColorIndex = 10
                        .Characters.Text = "Toggle Hidden Columns - Now UNHIDDEN"
                    End If
                End With
           End If
        End If
    Next c
End Sub

Toggle Hide Columns View 1Toggle Hidden Columns 2

Tagged with: , , , ,