Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet code
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet code
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! -----Original Message----- 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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet code
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) "Gareth" wrote in message ... 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! -----Original Message----- 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet code
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). Bob Phillips wrote: 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) "Gareth" wrote in message ... 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! -----Original Message----- 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" wrote in message ... 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 . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB code to copy sheet format to another sheet | Excel Discussion (Misc queries) | |||
Sheet Code Module: copying code to | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming | |||
unprotect sheet in code and make sheet visible | Excel Programming | |||
Help with some sheet code | Excel Programming |