Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a sheet using code
I have a spreadsheet with approximately 35 sheets all set up the same. I
want to hide a sheet if cell A11 is empty. I'm using the code from another post but my results are unfavorable. Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)', 'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I wasn't sure if I needed to alter the Array statement above but I've tried it every way I can and nothing works. I placed the code on Sheet2(Item1). I get a 'Subscript out of range' error message whenever data is entered or removed from cell A11 on sheet Item 1. What am I doing wrong!? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a sheet using code
Did you try this:
For Each sh In Sheets(Array("Sheet2"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") Be careful with your typing. They have to match--exactly! Don't you want this same procedure to hide/show the sheets? LJP wrote: I have a spreadsheet with approximately 35 sheets all set up the same. I want to hide a sheet if cell A11 is empty. I'm using the code from another post but my results are unfavorable. Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)', 'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I wasn't sure if I needed to alter the Array statement above but I've tried it every way I can and nothing works. I placed the code on Sheet2(Item1). I get a 'Subscript out of range' error message whenever data is entered or removed from cell A11 on sheet Item 1. What am I doing wrong!? Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a sheet using code
Dave...I tried the revision that your provided but no luck. I plead absolute
ignorance when it comes to code so my ability to help myself is minimal. Following is the code as it now appears: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2)"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub My current error message is: Wrong number of arguments or invalid property assignment. Additionally, I'm not sure I understand your question in your last post hide/show sheet. I was assuming that this procedure would 'unhide' the sheet if cell A11 was populated. Wrong assumption? "Dave Peterson" wrote: Did you try this: For Each sh In Sheets(Array("Sheet2"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") Be careful with your typing. They have to match--exactly! Don't you want this same procedure to hide/show the sheets? LJP wrote: I have a spreadsheet with approximately 35 sheets all set up the same. I want to hide a sheet if cell A11 is empty. I'm using the code from another post but my results are unfavorable. Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)', 'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I wasn't sure if I needed to alter the Array statement above but I've tried it every way I can and nothing works. I placed the code on Sheet2(Item1). I get a 'Subscript out of range' error message whenever data is entered or removed from cell A11 on sheet Item 1. What am I doing wrong!? Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a sheet using code
I screwed up the closing )'s when I added the other sheet names:
For Each sh In Sheets(Array("sheet2", _ "sheet2(Item1)", _ "sheet3(Item2)")) Sorry. But do watch out for the spelling. I'm not sure if there are spaces anywhere in those sheetnames. LJP wrote: Dave...I tried the revision that your provided but no luck. I plead absolute ignorance when it comes to code so my ability to help myself is minimal. Following is the code as it now appears: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2)"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub My current error message is: Wrong number of arguments or invalid property assignment. Additionally, I'm not sure I understand your question in your last post hide/show sheet. I was assuming that this procedure would 'unhide' the sheet if cell A11 was populated. Wrong assumption? "Dave Peterson" wrote: Did you try this: For Each sh In Sheets(Array("Sheet2"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") Be careful with your typing. They have to match--exactly! Don't you want this same procedure to hide/show the sheets? LJP wrote: I have a spreadsheet with approximately 35 sheets all set up the same. I want to hide a sheet if cell A11 is empty. I'm using the code from another post but my results are unfavorable. Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)', 'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I wasn't sure if I needed to alter the Array statement above but I've tried it every way I can and nothing works. I placed the code on Sheet2(Item1). I get a 'Subscript out of range' error message whenever data is entered or removed from cell A11 on sheet Item 1. What am I doing wrong!? Thanks. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a sheet using code
Thank you for the quick response...the good news is I'm no longer getting any
error messages! The bad news....nothing happens when data is entered or removed in cell A11. Any other suggestions? "Dave Peterson" wrote: I screwed up the closing )'s when I added the other sheet names: For Each sh In Sheets(Array("sheet2", _ "sheet2(Item1)", _ "sheet3(Item2)")) Sorry. But do watch out for the spelling. I'm not sure if there are spaces anywhere in those sheetnames. LJP wrote: Dave...I tried the revision that your provided but no luck. I plead absolute ignorance when it comes to code so my ability to help myself is minimal. Following is the code as it now appears: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2)"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub My current error message is: Wrong number of arguments or invalid property assignment. Additionally, I'm not sure I understand your question in your last post hide/show sheet. I was assuming that this procedure would 'unhide' the sheet if cell A11 was populated. Wrong assumption? "Dave Peterson" wrote: Did you try this: For Each sh In Sheets(Array("Sheet2"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") Be careful with your typing. They have to match--exactly! Don't you want this same procedure to hide/show the sheets? LJP wrote: I have a spreadsheet with approximately 35 sheets all set up the same. I want to hide a sheet if cell A11 is empty. I'm using the code from another post but my results are unfavorable. Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)', 'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I wasn't sure if I needed to alter the Array statement above but I've tried it every way I can and nothing works. I placed the code on Sheet2(Item1). I get a 'Subscript out of range' error message whenever data is entered or removed from cell A11 on sheet Item 1. What am I doing wrong!? Thanks. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a sheet using code
Dave: May I ask you (in an effort to learn) what 'Sheet2' refers to in the
first line of the revised section of code that you provided? It occurred to me that you may have written the code like you did based on my original post. I was using 'Sheet2' at the time because I wasn't sure if I was required to use 'Sheet2' or 'Sheet2(Item1)' in the line of code. I don't have a plain ole 'Sheet2' in my VBA workbook sheet listings. I'm sorry if I confused you but wanted to clarify in an effort to resolve. I feel so close to resolution I don't want to give up now! Thanks again. "LJP" wrote: Thank you for the quick response...the good news is I'm no longer getting any error messages! The bad news....nothing happens when data is entered or removed in cell A11. Any other suggestions? "Dave Peterson" wrote: I screwed up the closing )'s when I added the other sheet names: For Each sh In Sheets(Array("sheet2", _ "sheet2(Item1)", _ "sheet3(Item2)")) Sorry. But do watch out for the spelling. I'm not sure if there are spaces anywhere in those sheetnames. LJP wrote: Dave...I tried the revision that your provided but no luck. I plead absolute ignorance when it comes to code so my ability to help myself is minimal. Following is the code as it now appears: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2)"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub My current error message is: Wrong number of arguments or invalid property assignment. Additionally, I'm not sure I understand your question in your last post hide/show sheet. I was assuming that this procedure would 'unhide' the sheet if cell A11 was populated. Wrong assumption? "Dave Peterson" wrote: Did you try this: For Each sh In Sheets(Array("Sheet2"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") Be careful with your typing. They have to match--exactly! Don't you want this same procedure to hide/show the sheets? LJP wrote: I have a spreadsheet with approximately 35 sheets all set up the same. I want to hide a sheet if cell A11 is empty. I'm using the code from another post but my results are unfavorable. Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)', 'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I wasn't sure if I needed to alter the Array statement above but I've tried it every way I can and nothing works. I placed the code on Sheet2(Item1). I get a 'Subscript out of range' error message whenever data is entered or removed from cell A11 on sheet Item 1. What am I doing wrong!? Thanks. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide a sheet using code
Did you put the code behind the single worksheet that owns the range named All?
Did you allow macros to run when you opened the file? LJP wrote: Thank you for the quick response...the good news is I'm no longer getting any error messages! The bad news....nothing happens when data is entered or removed in cell A11. Any other suggestions? "Dave Peterson" wrote: I screwed up the closing )'s when I added the other sheet names: For Each sh In Sheets(Array("sheet2", _ "sheet2(Item1)", _ "sheet3(Item2)")) Sorry. But do watch out for the spelling. I'm not sure if there are spaces anywhere in those sheetnames. LJP wrote: Dave...I tried the revision that your provided but no luck. I plead absolute ignorance when it comes to code so my ability to help myself is minimal. Following is the code as it now appears: Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2)"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub My current error message is: Wrong number of arguments or invalid property assignment. Additionally, I'm not sure I understand your question in your last post hide/show sheet. I was assuming that this procedure would 'unhide' the sheet if cell A11 was populated. Wrong assumption? "Dave Peterson" wrote: Did you try this: For Each sh In Sheets(Array("Sheet2"), _ "Sheet2(Item1)", _ "Sheet3(Item2)") Be careful with your typing. They have to match--exactly! Don't you want this same procedure to hide/show the sheets? LJP wrote: I have a spreadsheet with approximately 35 sheets all set up the same. I want to hide a sheet if cell A11 is empty. I'm using the code from another post but my results are unfavorable. Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("A11"), Target) Is Nothing Then For Each sh In Sheets(Array("Sheet2")) If Target.Value = "" Then sh.Visible = xlSheetHidden If Target.Value < "" Then sh.Visible = xlSheetVisible Next sh End If End Sub The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)', 'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I wasn't sure if I needed to alter the Array statement above but I've tried it every way I can and nothing works. I placed the code on Sheet2(Item1). I get a 'Subscript out of range' error message whenever data is entered or removed from cell A11 on sheet Item 1. What am I doing wrong!? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to hide sheets | Excel Worksheet Functions | |||
hide code does not hide | Excel Discussion (Misc queries) | |||
code to hide hides too much | Excel Discussion (Misc queries) | |||
Hide VBA code help | Excel Worksheet Functions | |||
Hide Code | Excel Discussion (Misc queries) |