| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unhide a row in a worksheet using vba | Excel Discussion (Misc queries) | |||
| How do I unhide the PERSONAL.XLS worksheet? | Excel Worksheet Functions | |||
| How do you unhide the first three rows of a worksheet? | Excel Discussion (Misc queries) | |||
| how do i unhide a worksheet in excel 2003? unhide tab don't work | Excel Discussion (Misc queries) | |||
| Why can't I unhide rows 1 to 47 in a worksheet? | Excel Discussion (Misc queries) | |||
Linear Mode
