|
Hide/Show some worksheets
Is there a way to use VB code to hide/show a group of worksheets at once,
possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc). The current week will always be visible. Thanks! |
Hide/Show some worksheets
the following code will hide/show relevant woksheets on each workbook
opening press ALT+F11, open ThisWorkbook module, select Workbook_Open and paste the code Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws End Sub On 13 Sty, 14:53, Tony S. wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc). The current week will always be visible. *Thanks! |
Hide/Show some worksheets
Jarek,
When I run this I get "Run-time error '13' Type mismatch" I may have pasted the code in the wrong location. I opened the VB editor and did not see a Workbook_Open. I pasted it in the "ThisWorkbook" under "Microsoft Excel Objects". Should it be pasted in a module? I am running Excel 2003. "Jarek Kujawa" wrote: the following code will hide/show relevant woksheets on each workbook opening press ALT+F11, open ThisWorkbook module, select Workbook_Open and paste the code Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws End Sub On 13 Sty, 14:53, Tony S. wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc). The current week will always be visible. Thanks! |
Hide/Show some worksheets
double-click on ThisWorkbook module
you should see two windows to the right 1. (General) and 2. (Declarations) click on 1. (General) and select Workbook in 2. select "Open" from the list of events the result should be: Private Sub Workbook_Open() End Sub in between those 2 lines paste the following code: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws save and close yr file. from now on every time you open it and switch the macros on, the code should do what you expect HIH On 13 Sty, 15:58, Tony S. wrote: Jarek, When I run this I get "Run-time error '13' *Type mismatch" I may have pasted the code in the wrong location. I opened the VB editor and did not see a Workbook_Open. I pasted it in the "ThisWorkbook" under "Microsoft Excel Objects". Should it be pasted in a module? I am running Excel 2003. "Jarek Kujawa" wrote: the following code will hide/show relevant woksheets on each workbook opening press ALT+F11, open ThisWorkbook module, select Workbook_Open and paste the code Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets * * If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible * * Else ws.Visible = xlSheetVeryHidden * * End If Next ws End Sub On 13 Sty, 14:53, Tony S. wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc).. The current week will always be visible. *Thanks!- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
Hide/Show some worksheets
Jarek, Your explanation was perfect. I was able to follow it exactly.
However, after I save and reopen the file, I still get "Run-time error '13' Type mismatch". Does it have anything to do with how the sheets are named? I can rename them to anything, (like sht1, sht2, sh3, or #1, #2, #3 etc) but I stiil need one sheet named "Current Status" to be open and active. Is there a way to hide all the sheets and unhide the "Current Status" sheet? It doesn't necessarily need to run on opening the file. Ideally, they would all be hidden until I ran a keyboard macro. Thanks! "Jarek Kujawa" wrote: double-click on ThisWorkbook module you should see two windows to the right 1. (General) and 2. (Declarations) click on 1. (General) and select Workbook in 2. select "Open" from the list of events the result should be: Private Sub Workbook_Open() End Sub in between those 2 lines paste the following code: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws save and close yr file. from now on every time you open it and switch the macros on, the code should do what you expect HIH On 13 Sty, 15:58, Tony S. wrote: Jarek, When I run this I get "Run-time error '13' Type mismatch" I may have pasted the code in the wrong location. I opened the VB editor and did not see a Workbook_Open. I pasted it in the "ThisWorkbook" under "Microsoft Excel Objects". Should it be pasted in a module? I am running Excel 2003. "Jarek Kujawa" wrote: the following code will hide/show relevant woksheets on each workbook opening press ALT+F11, open ThisWorkbook module, select Workbook_Open and paste the code Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws End Sub On 13 Sty, 14:53, Tony S. wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc).. The current week will always be visible. Thanks!- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
Hide/Show some worksheets
did not expect any worksheet to have a name following different
pattern than 12-1-2008, etc. this probably IS the reason for Excel's behavior = run time error try to replace If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then with If (DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now ()) _ Or ws.Name = "Current Status" Then and let me know if it worked also if you don't want the whole process to be activated on workbook opening than delete the Private Sub Workbook_Open code from ThisWorkbook module instead Insert-Module and paste the code "here" under a new name (not Private Sub Workbook_Open!), say it is "sth" then Tools-Macro-Macros-find/select your "sth" macro, click on Options, insert a keyboard shortcut On 13 Sty, 22:11, Tony S. wrote: Jarek, Your explanation was perfect. I was able to follow it exactly. However, after I save and reopen the file, I still get *"Run-time error '13' * Type mismatch". Does it have anything to do with how the sheets are named? I can rename them to anything, (like sht1, sht2, sh3, or #1, #2, #3 etc) but I stiil need one sheet named "Current Status" to be open and active. Is there a way to hide all the sheets and unhide the "Current Status" sheet? It doesn't necessarily need to run on opening the file. Ideally, they would all be hidden until I ran a keyboard macro. Thanks! "Jarek Kujawa" wrote: double-click on ThisWorkbook module you should see two windows to the right 1. (General) and 2. (Declarations) click on 1. (General) and select Workbook in 2. select "Open" from the list of events the result should be: Private Sub Workbook_Open() End Sub in between those 2 lines paste the following code: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws save and close yr file. from now on every time you open it and switch the macros on, the code should do what you expect HIH On 13 Sty, 15:58, Tony S. wrote: Jarek, When I run this I get "Run-time error '13' *Type mismatch" I may have pasted the code in the wrong location. I opened the VB editor and did not see a Workbook_Open. I pasted it in the "ThisWorkbook" under "Microsoft Excel Objects". Should it be pasted in a module? I am running Excel 2003. "Jarek Kujawa" wrote: the following code will hide/show relevant woksheets on each workbook opening press ALT+F11, open ThisWorkbook module, select Workbook_Open and paste the code Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets * * If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible * * Else ws.Visible = xlSheetVeryHidden * * End If Next ws End Sub On 13 Sty, 14:53, Tony S. wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc).. The current week will always be visible. *Thanks!- Ukryj cytowany tekst - - Poka¿ cytowany tekst -- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
Hide/Show some worksheets
Jarek, I appreciate your time and efforts. I renamed each tab to be a date
only (i.e "12-1-2008", "12-8-2008", "12-15-2008" etc). When I run your revised code pasted in a module, it returns the "Compile error: Else without If" and the Else in the code is highlighted blue. With your first code it returns the same "Run-time error '13: Type mismatch". ************** Sub sht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws End Sub "Jarek Kujawa" wrote: did not expect any worksheet to have a name following different pattern than 12-1-2008, etc. this probably IS the reason for Excel's behavior = run time error try to replace If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then with If (DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now ()) _ Or ws.Name = "Current Status" Then and let me know if it worked also if you don't want the whole process to be activated on workbook opening than delete the Private Sub Workbook_Open code from ThisWorkbook module instead Insert-Module and paste the code "here" under a new name (not Private Sub Workbook_Open!), say it is "sth" then Tools-Macro-Macros-find/select your "sth" macro, click on Options, insert a keyboard shortcut On 13 Sty, 22:11, Tony S. wrote: Jarek, Your explanation was perfect. I was able to follow it exactly. However, after I save and reopen the file, I still get "Run-time error '13' Type mismatch". Does it have anything to do with how the sheets are named? I can rename them to anything, (like sht1, sht2, sh3, or #1, #2, #3 etc) but I stiil need one sheet named "Current Status" to be open and active. Is there a way to hide all the sheets and unhide the "Current Status" sheet? It doesn't necessarily need to run on opening the file. Ideally, they would all be hidden until I ran a keyboard macro. Thanks! "Jarek Kujawa" wrote: double-click on ThisWorkbook module you should see two windows to the right 1. (General) and 2. (Declarations) click on 1. (General) and select Workbook in 2. select "Open" from the list of events the result should be: Private Sub Workbook_Open() End Sub in between those 2 lines paste the following code: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws save and close yr file. from now on every time you open it and switch the macros on, the code should do what you expect HIH On 13 Sty, 15:58, Tony S. wrote: Jarek, When I run this I get "Run-time error '13' Type mismatch" I may have pasted the code in the wrong location. I opened the VB editor and did not see a Workbook_Open. I pasted it in the "ThisWorkbook" under "Microsoft Excel Objects". Should it be pasted in a module? I am running Excel 2003. "Jarek Kujawa" wrote: the following code will hide/show relevant woksheets on each workbook opening press ALT+F11, open ThisWorkbook module, select Workbook_Open and paste the code Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws End Sub On 13 Sty, 14:53, Tony S. wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc).. The current week will always be visible. Thanks!- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
Hide/Show some worksheets
don't know
looks like everything is OK to me ;-( On 14 Sty, 15:55, Tony S. wrote: Jarek, I appreciate your time and efforts. I renamed each tab to be a date only (i.e "12-1-2008", "12-8-2008", "12-15-2008" etc). When I run your revised code pasted in a module, it returns the "Compile error: Else without If" and the Else in the code is highlighted blue. With your first code it returns the same "Run-time error '13: Type mismatch". ************** Sub sht() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets * * If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible * * Else ws.Visible = xlSheetVeryHidden * * End If Next ws End Sub "Jarek Kujawa" wrote: did not expect any worksheet to have a name following different pattern than 12-1-2008, etc. this probably IS the reason for Excel's behavior = run time error try to replace If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then with If (DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now ()) _ Or ws.Name = "Current Status" Then and let me know if it worked also if you don't want the whole process to be activated on workbook opening than delete the Private SubWorkbook_Opencode from ThisWorkbook module instead Insert-Module and paste the code "here" under a new name (not Private SubWorkbook_Open!), say it is "sth" then Tools-Macro-Macros-find/select your "sth" macro, click on Options, insert a keyboard shortcut On 13 Sty, 22:11, Tony S. wrote: Jarek, Your explanation was perfect. I was able to follow it exactly. However, after I save and reopen the file, I still get *"Run-time error '13' * Type mismatch". Does it have anything to do with how the sheets are named? I can rename them to anything, (like sht1, sht2, sh3, or #1, #2, #3 etc) but I stiil need one sheet named "Current Status" to be open and active. Is there a way to hide all the sheets and unhide the "Current Status" sheet? It doesn't necessarily need to run on opening the file. Ideally, they would all be hidden until I ran a keyboard macro. Thanks! "Jarek Kujawa" wrote: double-click on ThisWorkbook module you should see two windows to the right 1. (General) and 2. (Declarations) click on 1. (General) and select Workbook in 2. select "Open" from the list of events the result should be: Private SubWorkbook_Open() End Sub in between those 2 lines paste the following code: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= Now() _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now() Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next ws save and close yr file. from now on every time you open it and switch the macros on, the code should do what you expect HIH On 13 Sty, 15:58, Tony S. wrote: Jarek, When I run this I get "Run-time error '13' *Type mismatch" I may have pasted the code in the wrong location. I opened the VB editor and did not see aWorkbook_Open. I pasted it in the "ThisWorkbook" under "Microsoft Excel Objects". Should it be pasted in a module? I am running Excel 2003. "Jarek Kujawa" wrote: the following code will hide/show relevant woksheets on each workbook opening press ALT+F11, open ThisWorkbook module, selectWorkbook_Openand paste the code Private SubWorkbook_Open() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets * * If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <= DateSerial(Year(Now()), Month(Now()), Day(Now())) _ And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible = xlSheetVisible * * Else ws.Visible = xlSheetVeryHidden * * End If Next ws End Sub On 13 Sty, 14:53, Tony S. wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc).. The current week will always be visible. *Thanks!- Ukryj cytowany tekst - - Poka¿ cytowany tekst -- Ukryj cytowany tekst - - Poka¿ cytowany tekst -- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
Hide/Show some worksheets
Jarek,
I was able to get the code I was looking for from Dave Peterson in the Programming section... Option Explicit Sub testme() Dim sh As Object 'make sure that there's always one sheet visible first Worksheets("Current status").Visible = xlSheetVisible For Each sh In ActiveWorkbook.Sheets If LCase(sh.Name) = LCase("current status") Then 'skip it Else If LCase(sh.Name) Like LCase("wk*") Then sh.Visible = xlSheetHidden End If End If Next sh End Sub Thanks for you atempt. "Tony S." wrote: Is there a way to use VB code to hide/show a group of worksheets at once, possibly using a wildcard? I have a worksheet that contains many sheets that need to be Veryhidden and Visible quickly and easily without having to go thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc). The current week will always be visible. Thanks! |
| All times are GMT +1. The time now is 08:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com