Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event problem...again
I posed this question on Friday, and received a number of responses that
didn't quite work. I dont think I did a very good job of asking my question. Here it is one more time: I have a workbook that contains many sheets. An input sheet contains various cells for user entry. Two of the cells are data validation lists with "Yes" or "No" as the choices. The cells are named "dval1" and "dval2", respectively. After any change to the input sheet, I want certain other sheets to be hidden or not, based on the selections in "dval1" and "dval2". I tried the following code: Private Sub Worksheet_Change(ByVal Target As Range) a = Range("dval1").Value If a = "Yes" Then Sheets("xxx").Visible = True Sheets("yyy").Visible = True Sheets("zzz").Visible = True Else Sheets("xxx").Visible = False Sheets("yyy").Visible = False Sheets("zzz").Visible = False End If b = Range("dval2").Value If b = "Yes" Then Sheets("ppp").Visible = True Sheets("qqq").Visible = True Sheets("rrr").Visible = True Else Sheets("ppp").Visible = False Sheets("qqq").Visible = False Sheets("rrr").Visible = False End If End Sub When I step through the code, it seems to go where it should, but the status of the sheets never changes. Nothing is protected. I'm confused. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event problem...again
I think my problem has todo with the worksheet size and recalc time. In a
simple sheet, the code works. The actual application is in a large workbook, with substantial recalc time. Is there a way to make the code run before a recalc. Also, I know how to set it, but how do I determine the calculation setting in code? "AVR" wrote: I posed this question on Friday, and received a number of responses that didn't quite work. I dont think I did a very good job of asking my question. Here it is one more time: I have a workbook that contains many sheets. An input sheet contains various cells for user entry. Two of the cells are data validation lists with "Yes" or "No" as the choices. The cells are named "dval1" and "dval2", respectively. After any change to the input sheet, I want certain other sheets to be hidden or not, based on the selections in "dval1" and "dval2". I tried the following code: Private Sub Worksheet_Change(ByVal Target As Range) a = Range("dval1").Value If a = "Yes" Then Sheets("xxx").Visible = True Sheets("yyy").Visible = True Sheets("zzz").Visible = True Else Sheets("xxx").Visible = False Sheets("yyy").Visible = False Sheets("zzz").Visible = False End If b = Range("dval2").Value If b = "Yes" Then Sheets("ppp").Visible = True Sheets("qqq").Visible = True Sheets("rrr").Visible = True Else Sheets("ppp").Visible = False Sheets("qqq").Visible = False Sheets("rrr").Visible = False End If End Sub When I step through the code, it seems to go where it should, but the status of the sheets never changes. Nothing is protected. I'm confused. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event problem...again
AVR,
Could it be that your code is looking for the value "Yes" (ie, capitalised) but you are typing in "yes" into the cell (uncapitalised)? It made a difference when i tried it! Good lcuk, Tim "AVR" wrote in message ... I posed this question on Friday, and received a number of responses that didn't quite work. I dont think I did a very good job of asking my question. Here it is one more time: I have a workbook that contains many sheets. An input sheet contains various cells for user entry. Two of the cells are data validation lists with "Yes" or "No" as the choices. The cells are named "dval1" and "dval2", respectively. After any change to the input sheet, I want certain other sheets to be hidden or not, based on the selections in "dval1" and "dval2". I tried the following code: Private Sub Worksheet_Change(ByVal Target As Range) a = Range("dval1").Value If a = "Yes" Then Sheets("xxx").Visible = True Sheets("yyy").Visible = True Sheets("zzz").Visible = True Else Sheets("xxx").Visible = False Sheets("yyy").Visible = False Sheets("zzz").Visible = False End If b = Range("dval2").Value If b = "Yes" Then Sheets("ppp").Visible = True Sheets("qqq").Visible = True Sheets("rrr").Visible = True Else Sheets("ppp").Visible = False Sheets("qqq").Visible = False Sheets("rrr").Visible = False End If End Sub When I step through the code, it seems to go where it should, but the status of the sheets never changes. Nothing is protected. I'm confused. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Change event problem | Excel Programming | |||
Problem w/ worksheet change event | Excel Programming | |||
Worksheet Change Event Problem | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |