| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change to control worksheet visibility
I am working in a sheet with 2 different data validation cells, named as
ranges "DV1" and "DV2" respectively. The only values for the cells are "Yes" or "No". When one of those 2 cells changes, I want to hide or unhide other sheets, based on the value in the changed cell. The following code is running, but the visibility of the sheets is unaffected. What am I doing wrong? 'Private Sub Worksheet_Change(ByVal Target As Range) ' ' a = Target.Address ' b = Target.Value ' x = Range("DV1").Address ' y = Range("DV2").Address ' ' If a = x Then ' ' If b = "Yes" Then ' Sheets("ShDV1A").Visible = True ' Sheets("ShDV1B").Visible = True ' Else ' Sheets("ShDV1A").Visible = False ' Sheets("ShDV1B").Visible = False ' End If ' ' ElseIf a = y Then ' ' If b = "Yes" Then ' Sheets("ShDV2A").Visible = True ' Sheets("ShDV2B").Visible = True ' Else ' Sheets("ShDV2A").Visible = False ' Sheets("ShDV2B").Visible = False ' End If ' ' End If ' ' 'End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change to control worksheet visibility
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value = "yes" Then Sheets("Sheet2").Visible = False Else Sheets("Sheet2").Visible = True End If End Sub Change to suit Corey.... "AVR" wrote in message ... I am working in a sheet with 2 different data validation cells, named as ranges "DV1" and "DV2" respectively. The only values for the cells are "Yes" or "No". When one of those 2 cells changes, I want to hide or unhide other sheets, based on the value in the changed cell. The following code is running, but the visibility of the sheets is unaffected. What am I doing wrong? 'Private Sub Worksheet_Change(ByVal Target As Range) ' ' a = Target.Address ' b = Target.Value ' x = Range("DV1").Address ' y = Range("DV2").Address ' ' If a = x Then ' ' If b = "Yes" Then ' Sheets("ShDV1A").Visible = True ' Sheets("ShDV1B").Visible = True ' Else ' Sheets("ShDV1A").Visible = False ' Sheets("ShDV1B").Visible = False ' End If ' ' ElseIf a = y Then ' ' If b = "Yes" Then ' Sheets("ShDV2A").Visible = True ' Sheets("ShDV2B").Visible = True ' Else ' Sheets("ShDV2A").Visible = False ' Sheets("ShDV2B").Visible = False ' End If ' ' End If ' ' 'End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change to control worksheet visibility
If I interpret your message correctly, you have assigned names to 2 cells
using names that are identical to a valid cell address. Excel could be confused trying to determine if Range("DV1") refers to the named range or to that actual cell. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AVR" wrote in message I am working in a sheet with 2 different data validation cells, named as ranges "DV1" and "DV2" respectively. The only values for the cells are "Yes" or "No". When one of those 2 cells changes, I want to hide or unhide other sheets, based on the value in the changed cell. The following code is running, but the visibility of the sheets is unaffected. What am I doing wrong? 'Private Sub Worksheet_Change(ByVal Target As Range) ' a = Target.Address ' b = Target.Value ' x = Range("DV1").Address ' y = Range("DV2").Address ' ' If a = x Then' ' If b = "Yes" Then ' Sheets("ShDV1A").Visible = True ' Sheets("ShDV1B").Visible = True ' Else ' Sheets("ShDV1A").Visible = False ' Sheets("ShDV1B").Visible = False ' End If ' ' ElseIf a = y Then' ' If b = "Yes" Then ' Sheets("ShDV2A").Visible = True ' Sheets("ShDV2B").Visible = True ' Else ' Sheets("ShDV2A").Visible = False ' Sheets("ShDV2B").Visible = False ' End If ' ' End If' 'End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change to control worksheet visibility
Thanks for the post. Do you know why my code didn't work?
"Corey" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("A1").Value = "yes" Then Sheets("Sheet2").Visible = False Else Sheets("Sheet2").Visible = True End If End Sub Change to suit Corey.... "AVR" wrote in message ... I am working in a sheet with 2 different data validation cells, named as ranges "DV1" and "DV2" respectively. The only values for the cells are "Yes" or "No". When one of those 2 cells changes, I want to hide or unhide other sheets, based on the value in the changed cell. The following code is running, but the visibility of the sheets is unaffected. What am I doing wrong? 'Private Sub Worksheet_Change(ByVal Target As Range) ' ' a = Target.Address ' b = Target.Value ' x = Range("DV1").Address ' y = Range("DV2").Address ' ' If a = x Then ' ' If b = "Yes" Then ' Sheets("ShDV1A").Visible = True ' Sheets("ShDV1B").Visible = True ' Else ' Sheets("ShDV1A").Visible = False ' Sheets("ShDV1B").Visible = False ' End If ' ' ElseIf a = y Then ' ' If b = "Yes" Then ' Sheets("ShDV2A").Visible = True ' Sheets("ShDV2B").Visible = True ' Else ' Sheets("ShDV2A").Visible = False ' Sheets("ShDV2B").Visible = False ' End If ' ' End If ' ' 'End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change to control worksheet visibility
The actual range names I used are not valid cell addresses...just text. In
order to shorten the code for the post, I cut the names down and accidentally created valid cell addresses. To avoid confusion, replace DV1 and DV2 with DataVal1 and DataVal2. My question still stands. What is wrong with my code? What I want the procedure to do is identify whether the change event was triggered by one of the 2 named cells, and if so, which one...then make the appropriate changes to the workbook by hiding / unhiding other sheets. There are many other cells in the sheet which may change, but those events should be ignored by the procedure. Thanks for your help. "Jim Cone" wrote: If I interpret your message correctly, you have assigned names to 2 cells using names that are identical to a valid cell address. Excel could be confused trying to determine if Range("DV1") refers to the named range or to that actual cell. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AVR" wrote in message I am working in a sheet with 2 different data validation cells, named as ranges "DV1" and "DV2" respectively. The only values for the cells are "Yes" or "No". When one of those 2 cells changes, I want to hide or unhide other sheets, based on the value in the changed cell. The following code is running, but the visibility of the sheets is unaffected. What am I doing wrong? 'Private Sub Worksheet_Change(ByVal Target As Range) ' a = Target.Address ' b = Target.Value ' x = Range("DV1").Address ' y = Range("DV2").Address ' ' If a = x Then' ' If b = "Yes" Then ' Sheets("ShDV1A").Visible = True ' Sheets("ShDV1B").Visible = True ' Else ' Sheets("ShDV1A").Visible = False ' Sheets("ShDV1B").Visible = False ' End If ' ' ElseIf a = y Then' ' If b = "Yes" Then ' Sheets("ShDV2A").Visible = True ' Sheets("ShDV2B").Visible = True ' Else ' Sheets("ShDV2A").Visible = False ' Sheets("ShDV2B").Visible = False ' End If ' ' End If' 'End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Limit Visibility of a worksheet | Excel Discussion (Misc queries) | |||
| worksheet image control appearance change (and back) with mousemov | Excel Programming | |||
| How to stop template worksheet visibility | Excel Programming | |||
| Reference to ActiveX control on worksheet requires full worksheet name | Excel Programming | |||
| Visibility Problem of Embedded Buttons in Worksheet | Excel Programming | |||
Linear Mode
