Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide sheets based upon cell value
I need help with 3 macros for hiding sheets:
1) I would like to hide several sheets based upon the value of a cell i.e. A1 within the sheet to possibly be hidden. 2) I need a macro to unhide all hidden sheets. Some sheets are not hidden. 3) I need to hide certain sheets using the name of the sheet. Thanks in advance for your help! Joe M. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide sheets based upon cell value
#1.
Dim wks as worksheet for each wks in activesheet.worksheets if lcase(wks.range("a1").value) = lcase("hideme") then wks.visible = xlsheethidden end if next wks #2 Dim wks as worksheet for each wks in activesheet.worksheets 'don't bother to see if it's visible or not wks.visible = xlsheetvisible next wks #3. Dim wks as worksheet for each wks in activesheet.worksheets if lcase(wks.name) like lcase("*somecommonstring*") then wks.visible = xlsheethidden end if next wks Remember that with #1 and #3, at least one sheet has to be visible. Joe M. wrote: I need help with 3 macros for hiding sheets: 1) I would like to hide several sheets based upon the value of a cell i.e. A1 within the sheet to possibly be hidden. 2) I need a macro to unhide all hidden sheets. Some sheets are not hidden. 3) I need to hide certain sheets using the name of the sheet. Thanks in advance for your help! Joe M. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide sheets based upon cell value
Dave,
I created a macro with only the code you showed for #1. I got this error: Object doesn't support this property or method (Error 438) What did I do wrong? Thanks, Joe M. "Dave Peterson" wrote: #1. Dim wks as worksheet for each wks in activesheet.worksheets if lcase(wks.range("a1").value) = lcase("hideme") then wks.visible = xlsheethidden end if next wks #2 Dim wks as worksheet for each wks in activesheet.worksheets 'don't bother to see if it's visible or not wks.visible = xlsheetvisible next wks #3. Dim wks as worksheet for each wks in activesheet.worksheets if lcase(wks.name) like lcase("*somecommonstring*") then wks.visible = xlsheethidden end if next wks Remember that with #1 and #3, at least one sheet has to be visible. Joe M. wrote: I need help with 3 macros for hiding sheets: 1) I would like to hide several sheets based upon the value of a cell i.e. A1 within the sheet to possibly be hidden. 2) I need a macro to unhide all hidden sheets. Some sheets are not hidden. 3) I need to hide certain sheets using the name of the sheet. Thanks in advance for your help! Joe M. -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide sheets based upon cell value
It was a typo (times 3).
Use: For Each wks In ActiveWorkbook.Worksheets not For Each wks In ActiveSheet.Worksheets Joe M. wrote: Dave, I created a macro with only the code you showed for #1. I got this error: Object doesn't support this property or method (Error 438) What did I do wrong? Thanks, Joe M. "Dave Peterson" wrote: #1. Dim wks as worksheet for each wks in activesheet.worksheets if lcase(wks.range("a1").value) = lcase("hideme") then wks.visible = xlsheethidden end if next wks #2 Dim wks as worksheet for each wks in activesheet.worksheets 'don't bother to see if it's visible or not wks.visible = xlsheetvisible next wks #3. Dim wks as worksheet for each wks in activesheet.worksheets if lcase(wks.name) like lcase("*somecommonstring*") then wks.visible = xlsheethidden end if next wks Remember that with #1 and #3, at least one sheet has to be visible. Joe M. wrote: I need help with 3 macros for hiding sheets: 1) I would like to hide several sheets based upon the value of a cell i.e. A1 within the sheet to possibly be hidden. 2) I need a macro to unhide all hidden sheets. Some sheets are not hidden. 3) I need to hide certain sheets using the name of the sheet. Thanks in advance for your help! Joe M. -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to hide sheets | Excel Discussion (Misc queries) | |||
Writing a macro to hide columns based on cell value | Excel Discussion (Misc queries) | |||
Hide or unhide sheets based on cell | Excel Discussion (Misc queries) | |||
Hide sheets based on Cell value | Excel Discussion (Misc queries) | |||
Formula or Macro to hide a row based on a cell value | Excel Discussion (Misc queries) |