Unhide worksheet IF

[Marilyn]

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

 

[Mike]

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]

Thanks Mike
I copied your code to the worksheet named Forms and it does not work. What
did I do wrong

 

[Gord Dibben]

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

 

[Marilyn]

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:129,131:132,146:146,158:162,164:166,168:168,171:171,173:173,189:189,194:195,306:306,308:308,343:343,345:345,350:350").EntireRow.Hidden = True
Else

Sheets("Discount
").Range("40:40,82:85,89:97,110:111,127:127,129:129,131:132,146:146,158:162,164:166,168:168,171:171,173:173,189:189,194:195,306:306,308:308,343:343,345: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]

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

 

[Marilyn]

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

 

[Marilyn]

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