Worksheet change to control worksheet visibility

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AVR - 26 Jan 2007 21:41 GMT

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 to this Message

Corey - 26 Jan 2007 22:22 GMT

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

>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
[quoted text clipped - 36 lines]
> '
> 'End Sub

Reply to this Message

AVR - 26 Jan 2007 22:30 GMT

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

> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Range("A1").Value = "yes" Then
[quoted text clipped - 49 lines]
> > '
> > 'End Sub

Reply to this Message

Jim Cone - 26 Jan 2007 22:28 GMT

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.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"AVR" <AVR@discussions.microsoft.com>
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 to this Message

AVR - 27 Jan 2007 00:27 GMT

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.

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

Reply to this Message