How do I toggle a button across a range of columns

[sodaboy]

I have twelve worksheets within a workbook, one for each month of the
year. The dates are listed down column A. I have ten adjacent columns
with data which I need to show / hide on a toggle button; then I have
to duplicate this ten times across the sheet and again duplicate the
entire thing for each sheet. I have the following code which works but
wondered if anyone can suggest a better way.

If cmdShowHide.Caption = "Hide" Then
Worksheets("January").Columns(14).Hidden = True
Worksheets("January").Columns(15).Hidden = True
Worksheets("January").Columns(16).Hidden = True
Worksheets("January").Columns(17).Hidden = True
Worksheets("January").Columns(18).Hidden = True
Worksheets("January").Columns(19).Hidden = True
Worksheets("January").Columns(20).Hidden = True
Worksheets("January").Columns(21).Hidden = True
Worksheets("January").Columns(22).Hidden = True
cmdShowHide.Caption = "Show"
ElseIf cmdShowHide.Caption = "Show" Then
Worksheets("January").Columns(14).Hidden = False
Worksheets("January").Columns(15).Hidden = False
Worksheets("January").Columns(16).Hidden = False
Worksheets("January").Columns(17).Hidden = False
Worksheets("January").Columns(18).Hidden = False
Worksheets("January").Columns(19).Hidden = False
Worksheets("January").Columns(20).Hidden = False
Worksheets("January").Columns(21).Hidden = False
Worksheets("January").Columns(22).Hidden = False
cmdShowHide.Caption = "Hide"
End If

I am a beginner at this so apologies if this is a bit simplistic.

Many thanks.

 

[Norman Jones]

Hi Sodaboy,

Try:

'================>>
Private Sub cmdShowHide_Click()
Me.Columns("N:V").Hidden = _
Not (Me.Columns("N:V").Hidden = True)
With cmdShowHide
If .Caption = "Show" Then
.Caption = "Hide"
Else
.Caption = "Show"
End If
End With

End Sub
'================>>

 

[sodaboy]

Thanks Norman,

It works a treat. Just so I know whats going on here, please could you
briefly explain how the 'NOT' function is evaluated.

Thanks again

 

[Norman Jones]

Hi Sodaboy,

It works a treat. Just so I know whats going on here, please could you
briefly explain how the 'NOT' function is evaluated.

Me.Columns("N:V").Hidden = _
Not (Me.Columns("N:V").Hidden = True)

In the right-hand side of this equation, the expression:
Me.Columns("N:V").Hidden = True
returns a Boolean True or False value according to the visible state of
columns N:V.

The Not operator serves to reverse (or negate) the returned Boolean value.
So the not operator is here used to toggle the visibility of the column
range by setting its hidden property to the reverse of the returned value.

The Not equality expression is a shorter, more elegant way of expressing the
following, equivalent If,,,Then,,,Else construct:

If Me.Columns("N:V").Hidden = True Then
Me.Columns("N:V").Hidden = False
ElseIf Me.Columns("N:V").Hidden = False Then
Me.Columns("N:V").Hidden = True
End If

See also the examples in VBA help under 'Not Operator'