Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I unhide/hide a sheet based on pull down selection? | Excel Discussion (Misc queries) | |||
Hide a row based on a cel value | Excel Discussion (Misc queries) | |||
how do I hide rows in excel based on values input on sheet ? | Excel Programming | |||
Hide/unhide sheet macro based on cell calculation | Excel Programming | |||
hide sheet? uncheck sheet tabs? or ??? | Excel Programming |