Hide a sheet based on value!
 

Hi...

Is it possible that a sheet can appear or be hidden based on the value in A1
being 1 or 0.

eg. If A1 = 1 in a sheet called summary then a sheet called Spa is hidden.
If A1 = 0 in a sheet called summary then a sheet called Spa is visible.

Thanks

Gordon.

Hide a sheet based on value!
 

Here is some code for you. It needs to be placed in the summary sheet (right
click the sheet tab and select view code). You can add more sheets by adding
more cases. I have 2 cases in my example...

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$1"
With Sheets("Spa")
If Target.Value = 1 Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetHidden
End If
End With
Case "$A$2"
With Sheets("Sheet3")
If Target.Value = 1 Then
.Visible = xlSheetVisible
Else
.Visible = xlSheetHidden
End If
End With
End Select
End Sub
--
HTH...

Jim Thomlinson


"Gordon" wrote:

Hi...

Is it possible that a sheet can appear or be hidden based on the value in A1
being 1 or 0.

eg. If A1 = 1 in a sheet called summary then a sheet called Spa is hidden.
If A1 = 0 in a sheet called summary then a sheet called Spa is visible.

Thanks

Gordon.

Hide a sheet based on value!
 

Jim's assumes you will change the value by editing the cell manually, with
code or possibly using DDE. It the value of the cell is determined by a
formula, you would need to use the calculate event. Then it would be

Private Sub Worksheet_Calculate()
Dim sh as Worksheet
set sh = worksheets("Summary")
With Sheets("Spa")
If sh.Range("A1").Value = 1 Then
.Visible = xlSheetVisible
Else sh.Range("A1").Value = 0 then
.Visible = xlSheetHidden
End If
End With
End Sub

Since you didn't mention any additional sheets, I didn't add that in my
example, but it would follow the same pattern.

--
Regards,
Tom Ogilvy

"Gordon" wrote:

Hi...

Is it possible that a sheet can appear or be hidden based on the value in A1
being 1 or 0.

eg. If A1 = 1 in a sheet called summary then a sheet called Spa is hidden.
If A1 = 0 in a sheet called summary then a sheet called Spa is visible.

Thanks

Gordon.

Hide a sheet based on value!
 

Hi Tom...

Thanks for this. It was a formula based cell value. However, when I insert
your code the following line goes red and it doesn't run.

Else sh.Range("A1").Value = 0 then

Any ideas? Thanks for your guidance so far!

Gordon.

"Tom Ogilvy" wrote:

Jim's assumes you will change the value by editing the cell manually, with
code or possibly using DDE. It the value of the cell is determined by a
formula, you would need to use the calculate event. Then it would be

Private Sub Worksheet_Calculate()
Dim sh as Worksheet
set sh = worksheets("Summary")
With Sheets("Spa")
If sh.Range("A1").Value = 1 Then
.Visible = xlSheetVisible
Else sh.Range("A1").Value = 0 then
.Visible = xlSheetHidden
End If
End With
End Sub

Since you didn't mention any additional sheets, I didn't add that in my
example, but it would follow the same pattern.

--
Regards,
Tom Ogilvy

"Gordon" wrote:

Hi...

Is it possible that a sheet can appear or be hidden based on the value in A1
being 1 or 0.

eg. If A1 = 1 in a sheet called summary then a sheet called Spa is hidden.
If A1 = 0 in a sheet called summary then a sheet called Spa is visible.

Thanks

Gordon.

Hide a sheet based on value!
 

Else sh.Range("A1").Value = 0 then

should be

Elseif sh.Range("A1").Value = 0 then

--
Regards,
Tom Ogilvy


"Gordon" wrote in message
...
Hi Tom...

Thanks for this. It was a formula based cell value. However, when I insert
your code the following line goes red and it doesn't run.

Else sh.Range("A1").Value = 0 then

Any ideas? Thanks for your guidance so far!

Gordon.

"Tom Ogilvy" wrote:

Jim's assumes you will change the value by editing the cell manually,
with
code or possibly using DDE. It the value of the cell is determined by a
formula, you would need to use the calculate event. Then it would be

Private Sub Worksheet_Calculate()
Dim sh as Worksheet
set sh = worksheets("Summary")
With Sheets("Spa")
If sh.Range("A1").Value = 1 Then
.Visible = xlSheetVisible
Else sh.Range("A1").Value = 0 then
.Visible = xlSheetHidden
End If
End With
End Sub

Since you didn't mention any additional sheets, I didn't add that in my
example, but it would follow the same pattern.

--
Regards,
Tom Ogilvy

"Gordon" wrote:

Hi...

Is it possible that a sheet can appear or be hidden based on the value
in A1
being 1 or 0.

eg. If A1 = 1 in a sheet called summary then a sheet called Spa is
hidden.
If A1 = 0 in a sheet called summary then a sheet called Spa is
visible.

Thanks

Gordon.