#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
unhide a row in a worksheet using vba Roxy Excel Discussion (Misc queries) 2 February 1st 08 01:41 AM
How do I unhide the PERSONAL.XLS worksheet? SteveyP66 Excel Worksheet Functions 1 August 9th 06 12:55 PM
How do you unhide the first three rows of a worksheet? jwwj232 Excel Discussion (Misc queries) 7 July 25th 06 02:25 PM
how do i unhide a worksheet in excel 2003? unhide tab don't work mikekeat Excel Discussion (Misc queries) 2 March 6th 06 04:36 AM
Why can't I unhide rows 1 to 47 in a worksheet? lcarr13 Excel Discussion (Misc queries) 4 July 18th 05 10:08 PM


All times are GMT +1. The time now is 03:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"