Sheet code

[Gareth]

I am struggling with some change event sheet code. I have
a data validation list in a cell and when I pick "Y" from
this list I want 2 hidden worksheets to become visible.
For some reason one of the sheets appears but not the
other, this is what I am using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("C20").Value = "Y" Then
Sheets("sheet1").Visible = True
Sheets("sheet2").Visible = True
Else
Sheets("sheet1").Visible = False
Sheets("sheet2").Visible = False
End If
End Sub

Any help gratefully received.

Gareth

 

[Bob Phillips]

Gareth,

I tried it and it works fine for me. Are you sure that the sheets are called
sheet1 and sheet2?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gareth]

Bob

names are correct but fotgot to mention that I'm on '97 (I
know there is a problem with the change event in '97 but
it seems to fire OK)

another funny thing, another cell has 3 sheets appear
when "Y" is selected and it works fine!

 

[Bob Phillips]

Gareth,

The 97 problem is that a cell with Data Validation does not trigger the
Change event.

That aside, I am perplexed. I just tried it on Excel 97 as well, and it
works fine there. Do you have any other event code messing it up (try
stepping through)?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Maybe it worked because of where you put the list.

From Debra Dalgleish's site:
http://contextures.com/xlDataVal08.html

In Excel 97, selecting an item from a Data Validation dropdown list does not
trigger a Change event, unless the list items have been typed in the Data
Validation dialog box. In this version, you can add a button to the worksheet,
and run the code by clicking the button. To see an example, go to the Sample
Worksheets page, and under the Filters heading, find Product List by Category,
and download the ProductsList97.xls file.

===
Debra puts a button near the data|validation cell that invokes the code (as a
workaround).