If..then...Hide Worksheet

[pegelos]

I'm working on a budget tracking template with multiple sheets. The 1st
worksheet has the YTD summary where managers allocate their $ to a
particular spending area (ie. advertising, travel etc.) There is a
worksheet for each spending area - 13 in total.
What I need help with is hiding worksheets/spending areas that do not
have any $ allocted - i.e: if Advertising has $0.00 allocted on YTD
summary, then hide the Advertsing Worksheet. I also want to be abel to
reverse and unhide the sheet if the manager realloctes $ to a spending
area.

One note...I have no control over the layout/set-up with multiple
sheets - it has been decided by the powers that be that this is the
format to use, so Ineed to work around it.

Any help would be greatly appreciated!

 

[Bob Phillips]

If you name the worksheets by the headings, such as Advertising, in column
A, and the YTD figures in column B, this worksheet event code does it

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Column = 2 Then
With Target
If .Value = 0 Then
Worksheets(.Offset(0, -1).Value).Visible = False
Else
Worksheets(.Offset(0, -1).Value).Visible = True
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

It's worksheet event code, so right-click on the sheet tab, select View
Code, and paste the code in there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pegelos]

Worked like a charm! Thanks for the help

 

[John]

Bob

I have a very similar situation, but need to hide columns

I want to be able to hide a column based on the value of a cell. For
example, IF A1="5A" hide the column headed 'MR' IF A1="7A" unhide the
column headed 'MR'

If a macro is needed, I want it to run when the Value is entered into
A1 without the user having to run the macro manually. I also want to
limit the column range. Ie: I only want to hide/unhide out to column
DA

Any help is appreciated

Regards
John

 

[Bob Phillips]

HI John,

I am not sure if you want to hide column MR for the 5A/7A and others for a
different value, but I'll give a start and you clarify in response.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Address = "$A$1" Then
With Target
Set oCell = Rows(1).Find("MR")
If Not oCell Is Nothing Then
If .Value = "5A" Then
Columns(oCell.Column).Hidden = True
ElseIf .Value = "7A" Then
Columns(oCell.Column).Hidden = False
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


It's worksheet event code, so right-click on the sheet tab, select View
Code, and paste the code in there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)