ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Unhide worksheet IF (https://www.excelbanter.com/excel-discussion-misc-queries/182117-unhide-worksheet-if.html)

Marilyn
Unhide worksheet IF
 
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
Unhide worksheet IF
 
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

Marilyn
Unhide worksheet IF
 
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

Gord Dibben
Unhide worksheet IF
 
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


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

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



Gord Dibben
Unhide worksheet IF
 
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




Marilyn
Unhide worksheet IF
 
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





Marilyn
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






All times are GMT +1. The time now is 09:58 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com