Solved: Unhide and activate sheet based on system date

[jdub12280]

goal: use function to unhide specific sheet based on system date. would need to compare created sheet names w/ system date.

Version: xl 2007 code needs to be compatible to users of 2003.

Example file: I have not included a sample file. My workbook has a sheet for each day of current month and a mtd sheet. name format of sheets will always be 3 digit month+space+1 or 2 digit day [ex: Oct 1 Oct 2 Oct 30 etc].


to step you through current project in summary:
-user opens a template
-user logs in (code copies template to a directory, renames file based on network username_month_year)
-code unhides ALL sheets in workbook (Oct 1 thru Oct 31)


My problem, is that user's do not always pay attention to the day they are supposed to be on, so I would like the code to unhide only the sheet whose name matches the current actual date. for example, current date = Oct 9, only Oct 9 would be unhidden. It might be better to reference the code name of the sheets rather the visible name.

Another option that i've considered (and also dont know how to accomplish) would be to unhide all sheets but restrict data entry on future dated sheets, and require password to edit past dated sheets. This seems to be more of a task, but maybe some1 here would think it be the better option.

Long and short, there are approximately 300 users that use these workbooks to track sales results, they are all linked to a report. The report by default only pulls results from current day and MTD, so if a user is entering results on the wrong date, their numbers won't be tracked.

To test for the results im looking for, any work book with multiple sheets can be used. sheet names would be Oct 1, Oct 2, Oct 3 etc... start with all sheets hidden except for a MTD sheet. then have the code (called by a login button in my case) to either unhide current day's sheet, or restrict editing to only current day's sheet.

Any help is appreciated, and thanks in advance! I have found several code samples here that have been very helpful to my project, this is the only one so far that i haven't seemed to stumble across.

Justin

[GTO]

Greetings Justin,

I see you joined a couple of months ago, but as this is your first post, let me say Welcome and a friendly Howdy from Arizona! You will find some utterly great and helpful folks here.

As to your question, see if this is of any help:
[vba]
Option Explicit

Sub exa()
Dim wksDay As Worksheet
Dim lngDay As Long
Dim strMon As String

lngDay = Day(Date)
strMon = MonthName(Month(Date), True)

On Error Resume Next
Set wksDay = ThisWorkbook.Worksheets(strMon & Chr(32) & lngDay)
On Error GoTo 0

If Not wksDay Is Nothing Then wksDay.Visible = xlSheetVisible
End Sub
[/vba]

Hope that helps,

Mark

[jdub12280]

Greetings Justin,

I see you joined a couple of months ago, but as this is your first post, let me say Welcome and a friendly Howdy from Arizona! You will find some utterly great and helpful folks here.

As to your question, see if this is of any help:
[vba]
Option Explicit

Sub exa()
Dim wksDay As Worksheet
Dim lngDay As Long
Dim strMon As String

lngDay = Day(Date)
strMon = MonthName(Month(Date), True)

On Error Resume Next
Set wksDay = ThisWorkbook.Worksheets(strMon & Chr(32) & lngDay)
On Error GoTo 0

If Not wksDay Is Nothing Then wksDay.Visible = xlSheetVisible
End Sub
[/vba]

Hope that helps,

Mark

Mark, thanks for the welcome! I have avoided posting previously as I was new, and didn't want to be a nuisance with questions that have already been answered. I have found lots of help just by searching and reading.

As to your code, it seems to do the trick as far as unhiding the correct sheet. My new question is this... if the user closes (and saves) the workbook with the sheet visible... then opens the next day, the code will unhide the correct sheet, but the previous day's sheet is still visible. Can this be modified to ensure that any day's except current are hidden? Assuming that it can, I will have a followup question to this, however, i'll lay it on one at a time. I like to leave myself room to figure it out .

Thanks again for the help!

From Oklahoma,

Justin

[Bob Phillips]

[vba]

Sub exa()
Dim wksDay As Worksheet
Dim sh As Worksheet
Dim lngDay As Long
Dim strMon As String

lngDay = Day(Date)
strMon = MonthName(Month(Date), True)

On Error Resume Next
Set wksDay = ThisWorkbook.Worksheets(strMon & Chr(32) & lngDay)
On Error GoTo 0

If Not wksDay Is Nothing Then

wksDay.Visible = xlSheetVisible
For Each sh In ActiveWorkbook.Worksheets

If Not sh.Name = wksDay.Name Then

sh.Visible = xlSheetHidden
End If
Next sh
End If

Set sh = Nothing
Set wksDay = Nothing
End Sub
[/vba]

[Bob Phillips]

[vba]

Sub exa()
Dim wksDay As Worksheet
Dim sh As Worksheet
Dim lngDay As Long
Dim strMon As String

lngDay = Day(Date)
strMon = MonthName(Month(Date), True)

On Error Resume Next
Set wksDay = ThisWorkbook.Worksheets(strMon & Chr(32) & lngDay)
On Error GoTo 0

If Not wksDay Is Nothing Then

wksDay.Visible = xlSheetVisible
For Each sh In ActiveWorkbook.Worksheets

If Not sh.Name = wksDay.Name Then

sh.Visible = xlSheetHidden
End If
Next sh
End If

Set sh = Nothing
Set wksDay = Nothing
End Sub
[/vba]

[jdub12280]

Thanks both for the help. it works as intended. i had to modify a little bit to make sure the MTD sheet was always visible. But other than that, very very helpful!

Justin

[jdub12280]

Ok, in preparing for a new month, i tested the code on a workbook, that did not have a sheet that the code would unhide.. today is Oct 24, i created a workbook for novermber... and to test i had nov 1 thru 6 visible, and a MTD sheet. As i read through the code, my understanding is that it would hide all sheets except for the current day. Well, in this instance, since Oct 24 does not exist in the workbook, the code did nothing. All sheets remained visible after running the function.

Secondly, just to make sure i didn't goof it up, I added 1 extra sheet labeled Oct 24, ran the code... and it worked. All sheets were hidden except Oct 24 and the MTD. Can you tell me what else needs to be modified to prevent the sheets from appearing even if the current day's sheet doesn't exist? I have modified the code below to handle protection, and to allow the MTD sheet to be viewable.

[vba]Sub exa()
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="JS1982"


Dim wksDay As Worksheet
Dim sh As Worksheet
Dim lngDay As Long
Dim strMon As String

lngDay = Day(Date)
strMon = MonthName(Month(Date), True)

On Error Resume Next
Set wksDay = ThisWorkbook.Worksheets(strMon & Chr(32) & lngDay)
On Error GoTo 0

If Not wksDay Is Nothing Then

wksDay.Visible = xlSheetVisible
For Each sh In ActiveWorkbook.Worksheets

If Not sh.Name = wksDay.Name Then

sh.Visible = xlSheetHidden
End If
Next sh
End If

Sheets("MTD Tally").Visible = xlSheetVisible
Set sh = Nothing
Set wksDay = Nothing

Application.ScreenUpdating = True
ActiveWorkbook.Protect Password:="JS1982"
End Sub[/vba]

Thanks again... i marked this thread as solved, but I was playing devil's advocate today...

Justin

[mdmackillop]

If the sheet does not exist, you obviously can't make use of its name. Create the name separately.
Also, unhide the MTD sheet first, to avoid problem of no visible sheets

[vba]
Sub exa()
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="JS1982"
Dim wksDay As Worksheet
Dim sh As Worksheet
Dim strDay As String
strDay = Format(Date, "mmm d")

Sheets("MTD Tally").Visible = xlSheetVisible
On Error Resume Next
Set wksDay = ThisWorkbook.Worksheets(strDay)
If Not wksDay Is Nothing Then wksDay.Visible = True
On Error GoTo 0

For Each sh In ActiveWorkbook.Worksheets
If Not sh.Name = strDay And Not sh.Name = "MTD Tally" Then
sh.Visible = xlSheetHidden
End If
Next sh

Set sh = Nothing
Set wksDay = Nothing
Application.ScreenUpdating = True
ActiveWorkbook.Protect Password:="JS1982"
End Sub

[/vba]

[jdub12280]

This worked beautifully... i am also trying to force myself to know why it works... If i am reading this correctly... the following code is saying that if the sheet exists it is visible... this is before we hide it... correct?

[VBA]Set wksDay = ThisWorkbook.Worksheets(strDay)
If Not wksDay Is Nothing Then wksDay.Visible = True
On Error Goto 0
[/VBA]

Thanks again...

Justin

[Bob Phillips]

It says, if the sheet exists make it visible, but not before it gets hidden as it is excluded from the sheet hidden.