Unhide worksheet IF
"Marilyn" wrote:
Gord
Again Thanks. I think this line ElseIf (Range("C24") = "Yes") is the
reason that when I select Yes it does not unhide the discount tab. Thank
for your time and expertise
"Gord Dibben" wrote:
Not sure how we got from hiding/unhiding a worksheet to all this.
I'll look at it later when I get some time.
The code Mike provided and my revision will hide the discount sheet if C24 is
blank.
Otherwise, any value other than blank will unhide the sheet.
Gord
On Wed, 2 Apr 2008 14:19:00 -0700, Marilyn
wrote:
Thanks Gord
Below is the code I had, I added the one you gave. If cell (C24) is blank
the discount sheet is hidden, if the cells says No the discount sheet is
unhidden but if the cells says yes nothing happens . Again thank you sooooo
much
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Intersect(Target, Range("B28,C24")) Is Nothing Then Exit Sub
If .Value = "Agreement" Then
Rows(32).Hidden = False
Rows(33).Hidden = True
Rows(39).Hidden = True
ElseIf .Value = "Master " Then
Rows(32).Hidden = True
Rows(33).Hidden = False
ElseIf (Range("C24") = "Yes") Then
Sheets("Discount
").Range("40:40,82:85,89:97,110:111,127:127,129:1 29,131:132,146:146,158:162,164:166,168:168,171:171 ,173:173,189:189,194:195,306:306,308:308,343:343,3 45:345,350:350").EntireRow.Hidden = True
Else
Sheets("Discount
").Range("40:40,82:85,89:97,110:111,127:127,129:1 29,131:132,146:146,158:162,164:166,168:168,171:171 ,173:173,189:189,194:195,306:306,308:308,343:343,3 45:345,350:350").EntireRow.Hidden = False
If Me.Range("C24").Value = "" Then
Worksheets("Discount €ś).Visible = xlSheetVeryHidden
Else
Worksheets("Discount").Visible = xlSheetVisible
End If
End If
End With
End Sub
"Gord Dibben" wrote:
The code Mike provided is not event code. It must be copied to a general module
and run manually.
Paste this into your "Forms" sheet module by right-click on sheet tab and "View
Code"
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("C24").Value = "" Then
Worksheets("Discount").Visible = xlSheetVeryHidden
Else
Worksheets("Discount").Visible = xlSheetVisible
End If
End Sub
Gord Dibben MS Excel MVP
On Wed, 2 Apr 2008 02:28:01 -0700, Marilyn
wrote:
Thanks Mike
I copied your code to the worksheet named Forms and it does not work. What
did I do wrong
"Mike" wrote:
Sub peekaboo()
On Error Resume Next
If Worksheets("Forms").Range("C24").Value = "" Then
Worksheets("Discount").Visible = xlSheetVeryHidden
Else
Worksheets("Discount").Visible = xlSheetVisible
End If
End Sub
"Marilyn" wrote:
Hi I have a work book (which has several worksheets) and the Worksheet
named €śDiscount€ť is hidden.. One of the tabs is named €śForm€ť . On C 24 of
the Form tab I have a dropdown validation list which includes 3 choices, (a
blank space , Yes, No) . What I want to do is if C24 says Yes or NO on the
Form worksheet then unhide the worksheet entitled €śDiscount€ť . If C 24 is
blank hide the worksheet named €śDiscount€ť Help with code please .Thanks in
advance
|