Make sheets Very Hidden After A Certian Date! 2

[APElliott]

Hello,

Can I make all worksheets in a workbook 'very hidden' if todays date is greater than a date in a certain cell.

Cheers,

Andrew

 

[xlbo]

In workbook open event

If date > range("A1&quot.value then
for each sht in thisworkbook.sheets
sht.visible = xlveryhidden
next

HOWEVER - you can't make ALL sheets invisible - you have to leave 1 visible

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Hi Andrew,

You can't hide all of the sheets in a workbook, but you can create a worksheet with a "Message" that informs your user that the workbook has expired. Make this the first worksheet in your workbook and set it's Visible property to xlSheetVeryHidden.

Then enter this code into a standard module in your VBA project:

Sub HideSheets_Now()
Dim sh As Worksheet
If Date < Sheets(&quot;Dates&quot;).Range(&quot;J2&quot;) Then Exit Sub
Sheets(1).Visible = True
For Each sh In ThisWorkbook.Worksheets
If sh.Index <> 1 Then ' This is your &quot;Message&quot; Sheet
    sh.Visible = xlSheetVeryHidden
End If
Next sh
End Sub

This will unhide your message sheet and hide all of the other sheets in the workbook so that they cannot be made visible again by using the Format->Sheets->Unhide... function.

In the Workbook_Open event for your workbook call the HideSheets_Now procedure.

And there you go!

Hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Star to Mike for the further explanation and code

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Thanx Geoff,

I'm honored to receive a star from the TopMaster of the week!

Congratulations again!





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[APElliott]

Cheers Guys,

Thanks

Andrew

 

[FaneDuru]

What will happen if the user does not Enable Macros?
I think it has to be a piece of code in the Before_Close event wich will make the sheets very hidden each time. On Autpen event the pages will become visible only if the user choose Enable Macros. Otherwise, only one sheet has to be visible telling him to choose "Enable Macros".....

I hope this helps...
FaneDuru'