MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to Hide Excel Sheet + Macro to create a Default sheet


Posted by Mac on November 02, 2001 6:56 AM

I have 10 worksheets in an Excel 2000 WorkBook. One is named "Info Sheet" and the rest are named "Sheet 1", "Sheet 2",...,"Sheet 9". I want a Macro that hides the "Info Sheet" before a certain date MM/DD/YYYY (and shows all the other sheets), and hides all the other sheets after date MM/DD/YYYY (and only show "Info Sheet"). I also want the Macro to execute automaically once a user opens the File/WorkBook.

I also want a macro that makes a Sheet the DEFAULT sheet of a Workbook (i.e. everytime I open the Workbook it opens at that worksheet). I want my workbook to use the CoverSheet as the default sheet.


Posted by Russell Hauf on November 02, 2001 1:37 PM

Put the code below in the Workbook_Open event. To get to the Workbook_Open, go to the VBA window and double click on "This Workbook" in the Project Explorer.

As for the code:

Private Sub Workbook_Open()
Dim dtTarget As Date
Dim fHideInfo As Boolean
Dim sht As Worksheet

dtTarget = DateSerial(2001, 11, 1)
' you could also put the date on a sheet somewhere and read it in

If Now() < dtTarget Then
ActiveWorkbook.Worksheets("Info Sheet").Visible = False
fHideInfo = True ' hide the info sheet
Else
ActiveWorkbook.Sheets("Info Sheet").Visible = True
End If

' If you are hiding the Info Sheet, then you want the
' other sheets to be visible. Otherwise, hide the other
' sheets (visible = false)
For Each sht In ActiveWorkbook.Worksheets
If sht.Name Like "Sheet*" Then
sht.Visible = fHideInfo
End If
Next sht

' Here is code for 'DEFAULT' sheet - also put in Workbook_Open
Sheets("Info Sheet").Activate
End Sub


Hope this helps,

Russell