Change 2 screen buttons to 1 toggle button

[glennpc]

I have 2 buttons on my screen (a Hide button and a Show button--created via insert shapes) that each are assigned a separate macro. One calls a macro to hide a column and the other shows that same column. Here are the subs:

[pre]

Sub HideSuggestedColumn()
Application.ScreenUpdating = False
Sheets("Lessons Learned").Columns("I:I").EntireColumn.Hidden = True
End Sub

Sub ShowSuggestedColumn()
Application.ScreenUpdating = False
Sheets("Lessons Learned").Columns("I:I").EntireColumn.Hidden = False
End Sub

[/pre]

Is there a way to combine these and set up the button on the screen to be a toggle? I could label it Hide/Show. Click it and it hides the column. Click it again and it shows the column. Can this be done?

 

[Hui]

Glennpc


This will do it for you

[pre]

Sub ToggleColumn()
Application.ScreenUpdating = False
Sheets("Lessons Learned").Columns("I:I").EntireColumn.Hidden = Not (Sheets("Lessons Learned").Columns("I:I").EntireColumn.Hidden)
Application.ScreenUpdating = True
End Sub

[/pre]

 

[glennpc]

Thanks! This works great.


I'm not sure I fully understand the logic of the line between the screenupdating lines.


Is it asking if the column is hidden, then make it not hidden? How does it work in the other direction?


I thought this would need to be done with some kind of If Then Else.. construction.


You guys are amazing!

 

[Hui]

Glennpc


When you have a property on the left side of the equals sign like

Sheets("Lessons Learned").Columns("I:I").EntireColumn.Hidden=

you set the value of Hidden to the value of what is after the equal sign


When a property is on the Right of an equal sign you can retrieve the properties current value

In this case

=Sheets("Lessons Learned").Columns("I:I").EntireColumn.Hidden

will return the current value of the hidden property as either True (Hidden) or False(Visible)


The Not modifier returns the opposite of a Boolean value

Not(True) = False

Not(False) = True


so they can be used together as above in:

Columns("I:I").EntireColumn.Hidden = Not (Columns("I:I").EntireColumn.Hidden)


or in plain english the value of Column I.hidden is the opposite of it's (Column I.hidden) current value