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
