Worksheet change event problem...again

[Guest]

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.

 

[Guest]

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?

 

[Tim]

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