xlVeryHidden and expire workbook

[jpirhalla]

Hello,
I have placed the following code in ThisWorkbook to hide all but a warning sheet if the user does not have Macros Enabled. Two questions:

(1) If macros are enabled, instead of Unhiding All sheets, how may I just want to unhide certain sheets (i.e.; "Submission Form", etc.)? Please provide example.

(2) I would like to include a subroutine that runs on Workbook_Open that does the following:
(a) on a specific date: show a msgbox saying "Expired", and prompt the user to enter a password.
(b) If the password is invalid, hide all sheets and save and close the workbook.

Any suggestions? Thanks!
-------------------------------------

Private Const dsWarningSheet As String = "Warning" 'This is the Macro warning worksheet

Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each ds In ActiveWorkbook.Sheets
If LCase(dsWarningSheet) = LCase(ds.Name) Then
ds.Visible = True
Else: ds.Visible = xlVeryHidden = 2
End If
Next
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal ds As Object, ByVal Target As Excel.Range)
If LCase(ds.Name) = LCase(dsWarningSheet) Then
For Each ds In ActiveWorkbook.Sheets
ds.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End If
End Sub

Private Sub workbook_open()
Sheets(dsWarningSheet).Select
For Each ds In ActiveWorkbook.Sheets
ds.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End Sub

 

[goofy78270]

(1) If macros are enabled, instead of Unhiding All sheets, how may I just want to unhide certain sheets (i.e.; "Submission Form", etc.)? Please provide example.

Instead of:

Private Sub workbook_open()
Sheets(dsWarningSheet).Select
For Each ds In ActiveWorkbook.Sheets
ds.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End Sub

Use the following and be sure to adjust the other routines in the same manner:

Private Sub workbook_open()
Sheets(dsWarningSheet).Select
dsSheet1.visible = True 'name all the sheets you want enabled
dsWarningSheet.visible = xlVeryHidden 'this must be done after another sheet has been set to visible
End Sub

(2) I would like to include a subroutine that runs on Workbook_Open that does the following:
(a) on a specific date: show a msgbox saying "Expired", and prompt the user to enter a password.
(b) If the password is invalid, hide all sheets and save and close the workbook.

If the date is stored within the workbook, you can simply create a comparison against it. But if you write this within a macro, you must secure the macro, or others will be able to see it if they desire. Another option is to simply passwrod protect the workbook after it expires.

If InputBox("What is the Password?", "This workbook is Expired", "Password") = "YourPassword" Then
... Add your code here

 

[jpirhalla]

Hi Goofy,
Thanks for the response.

RE: "Another option is to simply password protect the workbook after it expires" - can you please offer sample code for this?

 

[goofy78270]

Password protecting the workbook is manually done through:
Tools -> Options -> Security

You have the ability to require a password before the file is open, or allow the users to open the file, but as a read only, unless they have the password.