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
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
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
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
Check the simplest of things first. See if you accidently have a space typed in (leading or trailing) in the 'Payments' tab.
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.