Solved: VeryHiding Sheets after given date

[pushtheriver]

I am trying to hide 2 sheets in a 10 sheet workbook after a given date. This is in ThisWorkbook:
[VBA]
Option Explicit

Private Sub WorkBook_Open()
If Date >= DateSerial(2010, 9, 30) Then
Call Hidesheets

End If
ThisWorkbook.Save
End Sub

and this is in Module
Option Explicit

Sub Hidesheets()

Worksheets("Receipts").Visible = xlSheetVeryHidden

Worksheets("Payments").Visible = xlSheetVeryHidden

End Sub

Sub ShowSheets()
Worksheets("Receipts").Visible = True
End Sub
[/VBA]
The last is for unhiding .
I have been able to get it to work for one sheet but not two. No need for me to explain I am new to this. Just working my way through VBA for Dummies

Edit: VBA tags added to code

[GTO]

Greetings

Welcome to the forum; I am sure you'll be glad you joined, as there are some great folks here who are very helpful!

...The last is for unhiding .
I have been able to get it to work for one sheet but not two....

I didn't test with setting visibility to True, but let's use the supplied Constants. I didn't have any problem with...

ThisWorkbook Module:

Option Explicit
    
Private Sub WorkBook_Open()
    
    If Date >= DateSerial(2010, 9, 30) Then
        Call Hidesheets
        ThisWorkbook.Save
    End If
End Sub

Standard Module:

Option Explicit
    
Function Hidesheets()
    
    Worksheets("Receipts").Visible = xlSheetVeryHidden
    Worksheets("Payments").Visible = xlSheetVeryHidden
End Function
    
Function ShowSheets()
    
    Worksheets("Receipts").Visible = xlSheetVisible
    Worksheets("Payments").Visible = xlSheetVisible
End Function

Note that you can certainly leave the above two as Subs, I just changed to Functions so that they won't display to the user in the macro dialog box.

Hope that helps,

Mark

[GTO]

OOPs. I forgot to mention: When posting, look above where you are typing. The little green/white VBA button will plant tags in the msg body. If you put your code between these, its a lot easier to read :-)

[pushtheriver]

Brilliant service. Thank you so much Mark. Clearly I have a lot to learn. Pretty early in the morning for you too. Thanks again.
Alan

[pushtheriver]

Hi Mark

The workbook I want to use this VBA with is protected with password. Is it possible to use the solution you have provided? If so how do I unprotect then re protect the workbook?
Thanks in advance

Alan

[Bob Phillips]

Workbook or worksheets protected?

[pushtheriver]

Hi
Both are protected

Alan

[GTO]

Hi Alan,

From your description, I think we are still just talking about the sheets' visibility. Try:

Const PWD As String = "YourPassword"
 
Function Hidesheets()
 
    With ThisWorkbook
        .Unprotect Password:=PWD
        Worksheets("Receipts").Visible = xlSheetVeryHidden
        Worksheets("Payments").Visible = xlSheetVeryHidden
        '// Refer to the args in Help//
        .Protect Password:=PWD, Structure:=True, Windows:=False
    End With
End Function
 
Function ShowSheets()
    With ThisWorkbook
        .Unprotect Password:=PWD
        Worksheets("Receipts").Visible = xlSheetVisible
        Worksheets("Payments").Visible = xlSheetVisible
        .Protect Password:=PWD, Structure:=True, Windows:=False
    End With
End Function

Hope that helps,

Mark

PS - As you are leaving the password in the code, I personally do not use one of my own passwords, as excel is simply not a secure environment...

[pushtheriver]

Hi Mark
I have tried that but I am getting Run Time Error 9
"Subscript out of range."

The date triggers the hiding of the "Receipts" tab but not the "Payments" tab - see VBA below.

In This WorkBook
[VBA]
Private Sub WorkBook_Open()

If Date >= DateSerial(2010, 1, 30) Then
Call Hidesheets
ThisWorkbook.Save
End If
End Sub

[/VBA]

In Module

[VBA]Option Explicit
Const PWD As String = "Ye0\Wy4"

Function Hidesheets()

With ThisWorkbook
.Unprotect Password:=PWD
Worksheets("Receipts").Visible = xlSheetVeryHidden
Worksheets("Payments").Visible = xlSheetVeryHidden
'// Refer to the args in Help//
.Protect Password:=PWD, Structure:=True, Windows:=False
End With
End Function

Function ShowSheets()
With ThisWorkbook
.Unprotect Password:=PWD
Worksheets("Receipts").Visible = xlSheetVisible
Worksheets("Payments").Visible = xlSheetVisible
.Protect Password:=PWD, Structure:=True, Windows:=False
End With
End Function
[/VBA]

Very grateful for your time.

Alan

[pushtheriver]

I notice also that the workbook is not protected when it runs.

Alan

[GTO]

Check the simplest of things first. See if you accidently have a space typed in (leading or trailing) in the 'Payments' tab.

[GTO]

Specifically what are you looking to protect? For instance, w/the args I included, it would not be possibble to insert/delete a sheet (these options will be greyed out on the context/right-click menu.

[pushtheriver]

Thank you! It all appears to be doing what I want now.

I am very grateful for your help.

[GTO]

Very glad to help :-)