This is about macro I have used quite often in the last few years that achieves two things:
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.
[vb]
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
[/vb]
