Worksheet change to control worksheet visibility

[AVR]

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

 

[Corey]

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....

 

[Jim Cone]

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" <[email protected]>
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

 

[AVR]

Thanks for the post. Do you know why my code didn't work?

 

[AVR]

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.