Re-post: Code to make sheet very hidden based on date

[wx4usa]

I need some code to hide a sheet if a date has past. Date is in Cell
a1 of sheet 1 and sheet 2 needs to be very hidden. I would rather
not use regular conditional formatting since formats can be copied and
pasted thus changing cell formats.

I would like to make the worksheet very hidden if possible.

If you can provide the code, please tell me where to paste it. I am
new to VB for Excel

Thanks

 

[JonShops]

This will do it. You might investigate using protection on the sheets as
well. Nice thing here is the sheets just look like nothing is there. Bad
thing is that you get a good pause on opening if in fact a sheet is being
hidden. Once hidden, though, no extra pauses.

Also note that you must sign this file on the specific machine and set
security to allow execution of signed macro files, or else a person can just
not run macro and still see earlier date. Or, you can run the macro every
night before anyone has chance to see.

Note that this is not really that secure. Better would be to have
lower-trust users edit on a signle-sheet workbook then you come in at night,
open both that workbook and your workbook with the master collection of
dates, tile horizontally within the Excel window, and just slide their edited
sheet into your protected master workbook with all dates. If you are system
admin you can even have them as limited user accounts (in Windows OS) and get
real protection this way...just go into "Low Trust User's Documents" (with
real name, of course) and into your own My Documents, open the two in Excel,
and slide across the sheet for latest day. Then, have a blank version as
Blank tab in your master workbook, and slide the blank back across while
pressing CTRL to make a copy instead of just moving.

But, here is code to do as you requested.

Private Sub Workbook_Open()
Dim EachSheet As Worksheet
Dim EachColumn As Range
Dim ScreenStatus As Boolean
Dim WindowStatus As Long

WindowStatus = Application.WindowState
Application.WindowState = xlMinimized 'Hide contents while hiding sheet
contents
ScreenStatus = Application.ScreenUpdating
Application.ScreenUpdating = False 'Don't try to update screen
(redundant if in fact using WindowState xlMinimized)
For Each EachSheet In Worksheets
If Int(CDate(EachSheet.Range("A1").Value)) <= Int(Now) And
EachSheet.Range("A1").EntireColumn.ColumnWidth <> 0 Then
For Each EachColumn In EachSheet.Columns
EachColumn.ColumnWidth = 0
Next
End If
Next
Application.ScreenUpdating = ScreenStatus
Application.WindowState = WindowStatus

End Sub