Hide sheets by cell value

[A. Karatas]

I am making a database in excel in which two types of information is
beeing produced. Yearly figures and year to date figures. Both type's
consist of multiple sheets (more that 20 is normal), with details
about certain markets. By a dropdown menu i would like to have a
selection on seeing only the sheets in producing the yearly figures or
the YTD figures.

I have the following working on one sheet, but I don't want to write
it for each sheet with a different name. I also noticed that when I
change the sheetname the macro quits working.

Dim sh As Worksheet
Set sh = Worksheets("Assumptions")
With Sheets("P&L current")
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If

The dropdown menu puts the value 1 or 2 in cell Ae1. is it possible
that a macro looks in every sheet for cell AE1 and hides or unhides it
by the value 1 or 2???

 

[Tom Ogilvy]

To controll the visibility of each worksheet except Assumptions by looking at
cell AE1 of that sheet, use this code:
Sub ControlSheets()
Dim sh as Worksheet
for each sh in worksheets
if sh.Name <> "Assumptions" then
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If
end if
Next
end Sub

 

[A. Karatas]

hello Tom,

for the folowwing code i get the following error

.Visible = xlSheetVisible

= invalid or unqualified error????

 

[Gary Keramidas]

try putting sh before the . visible on the 2 lines where it appears.

 

[Tom Ogilvy]

I copied your code - guess I didn't clean it up enough.

Sub ControlSheets()
Dim sh as Worksheet
for each sh in worksheets
if sh.Name <> "Assumptions" then
If sh.Range("Ae1").Value = 2 Then
sh.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
sh.Visible = xlSheetHidden
End If
end if
Next
end Sub

Regards,
Tom Ogilvy

 

[Damien McBain]

I am making a database in excel in which two types of information is
beeing produced. Yearly figures and year to date figures. Both type's
consist of multiple sheets (more that 20 is normal), with details
about certain markets. By a dropdown menu i would like to have a
selection on seeing only the sheets in producing the yearly figures or
the YTD figures.

I have the following working on one sheet, but I don't want to write
it for each sheet with a different name. I also noticed that when I
change the sheetname the macro quits working.

Dim sh As Worksheet
Set sh = Worksheets("Assumptions")
With Sheets("P&L current")
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If

The dropdown menu puts the value 1 or 2 in cell Ae1. is it possible
that a macro looks in every sheet for cell AE1 and hides or unhides it
by the value 1 or 2???

Is there something on each worksheet that defines it as ytd or yearly other
than the "1" or "2" that you manually allocate? Or are you manually
inputting "1" or "2" on every worksheet to determine that?

This will make all the workshseets with 2 in A1 visible and all the
worksheets with 1 in A1 invisible (and will ignore sheets with neither 2
nor 1 in A1):

Sub test()
For Each ws In Worksheets 'looks through the whole collection of worksheets
If ws.Range("A1") = 2 Then
ws.Visible = False
ElseIf ws.Range("A1") = 1 Then
ws.Visible = True
End If
Next ws
End Sub

HTH

 

[A. Karatas]

I copied your code - guess I didn't clean it up enough.

Sub ControlSheets()
Dim sh as Worksheet
for each sh in worksheets
if sh.Name <> "Assumptions" then
If sh.Range("Ae1").Value = 2 Then
sh.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
sh.Visible = xlSheetHidden
End If
end if
Next
end Sub

Regards,
Tom Ogilvy







- Tekst uit oorspronkelijk bericht weergeven -

Thankx guys, it works great

 

[A. Karatas]

A. Karataswrote:




Is there something on each worksheet that defines it as ytd or yearly other
than the "1" or "2" that you manually allocate? Or are you manually
inputting "1" or "2" on every worksheet to determine that?

This will make all the workshseets with 2 in A1 visible and all the
worksheets with 1 in A1 invisible (and will ignore sheets with neither 2
nor 1 in A1):

Sub test()
For Each ws In Worksheets 'looks through the whole collection of worksheets
If ws.Range("A1") = 2 Then
ws.Visible = False
ElseIf ws.Range("A1") = 1 Then
ws.Visible = True
End If
Next ws
End Sub

HTH

The dropdown box can make 2 selections. Year which is 1 and YTD which
is 2

A formula on the sheets for year figures searches for the value on the
sheet assumption which says = if(assumptions!AE1=2;1;2) and the
figures YTD looks only at AE1.

In this way it is working super