PDA

View Full Version : Solved: VeryHiding Sheets after given date



pushtheriver
04-05-2010, 07:03 AM
I am trying to hide 2 sheets in a 10 sheet workbook after a given date. This is in ThisWorkbook:

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

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
04-05-2010, 07:48 AM
Greetings :hi:

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
04-05-2010, 07:51 AM
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
04-05-2010, 08:25 AM
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
04-06-2010, 06:36 AM
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:thumb

Alan

Bob Phillips
04-06-2010, 07:05 AM
Workbook or worksheets protected?

pushtheriver
04-06-2010, 07:14 AM
Hi
Both are protected

Alan

GTO
04-06-2010, 07:48 AM
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
04-06-2010, 08:28 AM
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

Private Sub WorkBook_Open()

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



In Module

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


Very grateful for your time.

Alan

pushtheriver
04-06-2010, 08:34 AM
I notice also that the workbook is not protected when it runs.

Alan

GTO
04-06-2010, 08:34 AM
Check the simplest of things first. See if you accidently have a space typed in (leading or trailing) in the 'Payments' tab.

GTO
04-06-2010, 08:36 AM
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
04-07-2010, 10:09 AM
Thank you! It all appears to be doing what I want now.

I am very grateful for your help. :bow:

GTO
04-07-2010, 02:44 PM
Very glad to help :-)