Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto hide sheets
Hello
Basic and simple question, but have forgotten. How do you hide any open sheets, when opening a workbook? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto hide sheets
Hello tcbootneck, Add the following code to your Workbook's Open event procedure. Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks This will check each Worksheet and test if it is visible. If it is visible then the macro will hide the Worksheet. Your final code result will look like this... Private Sub Workbook_Open() Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks End Sub Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=553001 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto hide sheets
Hi Leith
Just the job. Thanks "Leith Ross" wrote: Hello tcbootneck, Add the following code to your Workbook's Open event procedure. Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks This will check each Worksheet and test if it is visible. If it is visible then the macro will hide the Worksheet. Your final code result will look like this... Private Sub Workbook_Open() Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks End Sub Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=553001 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto hide sheets
Don't you get an error or am I missing something? I don't think you can hide
all worksheets at the same time. I assumed Leith had an oversight. Thought it should be something like: Private Sub Workbook_Open() Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Name < "Sheet1" Then If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden End If Next Wks End Sub Greg "tcbootneck" wrote: Hi Leith Just the job. Thanks "Leith Ross" wrote: Hello tcbootneck, Add the following code to your Workbook's Open event procedure. Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks This will check each Worksheet and test if it is visible. If it is visible then the macro will hide the Worksheet. Your final code result will look like this... Private Sub Workbook_Open() Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks End Sub Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=553001 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto hide sheets
A workbooks MUST have one visible sheet, either a Sheet or a Worksheet. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto hide sheets
I take that as a confirmation. Perhaps tcbootneck has a chart sheet and so
didn't get an error? Greg "AA2e72E" wrote: A workbooks MUST have one visible sheet, either a Sheet or a Worksheet. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto hide sheets
Hi Leith
I am really new to VBA Programming, and seems to struggling with connecting the dots at this point. The macro works really well, but I was wondering if I could add exclusionary criteria. I want the Macro to ignore a specific sheet when it is going through the 15 sheets in workbook. Thanks John -- Hisam A Nhoj "Leith Ross" wrote: Hello tcbootneck, Add the following code to your Workbook's Open event procedure. Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks This will check each Worksheet and test if it is visible. If it is visible then the macro will hide the Worksheet. Your final code result will look like this... Private Sub Workbook_Open() Dim Wks As Worksheet For Each Wks In ThisWorkbook.Worksheets If Wks.Visible = xlSheetVisible Then Wks.Visible = xlSheetHidden Next Wks End Sub Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=553001 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto hide data | Excel Discussion (Misc queries) | |||
auto hide | Excel Discussion (Misc queries) | |||
Hide all sheets but selected sheets - an example | Excel Programming | |||
Auto Hide | Excel Programming | |||
How to auto hide an empty row with VBA? | Excel Programming |