VBA
 

Is it possible to use a VBA code to make sheets in an excel workbook active
or hidden based on current date ?

Using Excel 2000

Thanks in advance for any guidance .........

VBA
 

In article ,
says...
Is it possible to use a VBA code to make sheets in an excel workbook active
or hidden based on current date ?

Using Excel 2000

Thanks in advance for any guidance .........


Yes.

For more specific suggestions, you will have to be more specific in
what you want.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

VBA
 

I have a Excel workbook containing 4 work sheets.

The worksheets contain info for weekly time records.

The time sheets are for a 7 day period.

Therefore I am collecting daily work hours for a 28 day work period on a
weekly basis.

I want employees to be able to open the workbook and only the sheet(s) for
the current time to be active. If possible I wish to hide the other sheets
based on the current date.

An example would be:

Workbook is set up with the following sheets:
Sheet 1 is a weekly time sheet for week of 09/26/2005 through 10/02/2005.
Sheet 2 is a weekly time sheet for week of 10/03/2005 through 10/09/2005.
Sheet 3 is a weekly time sheet for week of 10/10/2005 through 10/16/2005.
Sheet 2 is a weekly time sheet for week of 10/17/2005 through 10/23/2005.

When an employee opens the workbook during peiod of 09/26/2005 through
10/02/2005 I want Sheet 1 to be the only visible sheet.

When an employee opens the workbook during peiod of 10/03/2005 through
10/09/2005 I want both Sheet 1 and Sheet 2 to be the only visible sheets.

When an employee opens the workbook during peiod of 10/10/2005 through
10/16/2005 I want Sheet 1, Sheet 2 and Sheet 3 to be the only visible sheets.

When an employee opens the workbook during period 10/17/2005 through
10/23/2005 I want all 4 sheets to be visible.

I was wanting to use a VBA code to control the visible and hidden behavior
of the individual worksheets based on current date that workbook is accessed.

Any suggestions ?

Thanks





"Tushar Mehta" wrote:

In article ,
says...
Is it possible to use a VBA code to make sheets in an excel workbook active
or hidden based on current date ?

Using Excel 2000

Thanks in advance for any guidance .........


Yes.

For more specific suggestions, you will have to be more specific in
what you want.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

VBA
 

Hopefully, you are OK renaming the worksheets as mm-dd-yy where the
date is the Monday of each week. So, the worksheets would be named 09-
19-05, 10-03-05, etc.

Put this lightly tested code in the code module of ThisWorkbook.

Private Sub Workbook_Open()
Dim CurrMonday As String, i As Integer
CurrMonday = Format(Date - Weekday(Date, vbMonday) + 1, "mm-dd-yy")
With ActiveWorkbook.Worksheets
.Item(CurrMonday).Visible = True
For i = 1 To .Count
With .Item(i)
If IsDate(.Name) Then
.Visible = IIf(CDate(.Name) <= CDate(CurrMonday), _
xlSheetVisible, xlSheetHidden) 'xlsheetveryhidden
Else
.Visible = xlSheetHidden 'xlsheetveryhidden
End If
End With
Next i
End With
End Sub

Whenever the workbook is opened, the code will hide all worksheets
except those whose names represent already passed Mondays.

Worksheets not named in the correct format will be hidden.

The hidden worksheets can be made visible through Format | Sheet
Unhide... To block that capability replace the references to
xlSheetHidden by xlSheetVeryHidden.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have a Excel workbook containing 4 work sheets.

The worksheets contain info for weekly time records.

The time sheets are for a 7 day period.

Therefore I am collecting daily work hours for a 28 day work period on a
weekly basis.

I want employees to be able to open the workbook and only the sheet(s) for
the current time to be active. If possible I wish to hide the other sheets
based on the current date.

An example would be:

Workbook is set up with the following sheets:
Sheet 1 is a weekly time sheet for week of 09/26/2005 through 10/02/2005.
Sheet 2 is a weekly time sheet for week of 10/03/2005 through 10/09/2005.
Sheet 3 is a weekly time sheet for week of 10/10/2005 through 10/16/2005.
Sheet 2 is a weekly time sheet for week of 10/17/2005 through 10/23/2005.

When an employee opens the workbook during peiod of 09/26/2005 through
10/02/2005 I want Sheet 1 to be the only visible sheet.

When an employee opens the workbook during peiod of 10/03/2005 through
10/09/2005 I want both Sheet 1 and Sheet 2 to be the only visible sheets.

When an employee opens the workbook during peiod of 10/10/2005 through
10/16/2005 I want Sheet 1, Sheet 2 and Sheet 3 to be the only visible sheets.

When an employee opens the workbook during period 10/17/2005 through
10/23/2005 I want all 4 sheets to be visible.

I was wanting to use a VBA code to control the visible and hidden behavior
of the individual worksheets based on current date that workbook is accessed.

Any suggestions ?

Thanks





"Tushar Mehta" wrote:

In article ,
says...
Is it possible to use a VBA code to make sheets in an excel workbook active
or hidden based on current date ?

Using Excel 2000

Thanks in advance for any guidance .........


Yes.

For more specific suggestions, you will have to be more specific in
what you want.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

VBA
 

Thanks for your response:

I renamed the worksheets to date format and inserted code into
module for ThisWorkbook.

When I open the workbook I get:

Run-time error '9':
Subscript out of range

When I debug the below command is shown in yellow highlight.
..Item(CurrMonday).Visible = True

Any suggestions :)





"Tushar Mehta" wrote:

Hopefully, you are OK renaming the worksheets as mm-dd-yy where the
date is the Monday of each week. So, the worksheets would be named 09-
19-05, 10-03-05, etc.

Put this lightly tested code in the code module of ThisWorkbook.

Private Sub Workbook_Open()
Dim CurrMonday As String, i As Integer
CurrMonday = Format(Date - Weekday(Date, vbMonday) + 1, "mm-dd-yy")
With ActiveWorkbook.Worksheets
.Item(CurrMonday).Visible = True
For i = 1 To .Count
With .Item(i)
If IsDate(.Name) Then
.Visible = IIf(CDate(.Name) <= CDate(CurrMonday), _
xlSheetVisible, xlSheetHidden) 'xlsheetveryhidden
Else
.Visible = xlSheetHidden 'xlsheetveryhidden
End If
End With
Next i
End With
End Sub

Whenever the workbook is opened, the code will hide all worksheets
except those whose names represent already passed Mondays.

Worksheets not named in the correct format will be hidden.

The hidden worksheets can be made visible through Format | Sheet
Unhide... To block that capability replace the references to
xlSheetHidden by xlSheetVeryHidden.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have a Excel workbook containing 4 work sheets.

The worksheets contain info for weekly time records.

The time sheets are for a 7 day period.

Therefore I am collecting daily work hours for a 28 day work period on a
weekly basis.

I want employees to be able to open the workbook and only the sheet(s) for
the current time to be active. If possible I wish to hide the other sheets
based on the current date.

An example would be:

Workbook is set up with the following sheets:
Sheet 1 is a weekly time sheet for week of 09/26/2005 through 10/02/2005.
Sheet 2 is a weekly time sheet for week of 10/03/2005 through 10/09/2005.
Sheet 3 is a weekly time sheet for week of 10/10/2005 through 10/16/2005.
Sheet 2 is a weekly time sheet for week of 10/17/2005 through 10/23/2005.

When an employee opens the workbook during peiod of 09/26/2005 through
10/02/2005 I want Sheet 1 to be the only visible sheet.

When an employee opens the workbook during peiod of 10/03/2005 through
10/09/2005 I want both Sheet 1 and Sheet 2 to be the only visible sheets.

When an employee opens the workbook during peiod of 10/10/2005 through
10/16/2005 I want Sheet 1, Sheet 2 and Sheet 3 to be the only visible sheets.

When an employee opens the workbook during period 10/17/2005 through
10/23/2005 I want all 4 sheets to be visible.

I was wanting to use a VBA code to control the visible and hidden behavior
of the individual worksheets based on current date that workbook is accessed.

Any suggestions ?

Thanks





"Tushar Mehta" wrote:

In article ,
says...
Is it possible to use a VBA code to make sheets in an excel workbook active
or hidden based on current date ?

Using Excel 2000

Thanks in advance for any guidance .........


Yes.

For more specific suggestions, you will have to be more specific in
what you want.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

VBA
 

Correction:

My stupidity

When I renamed the worksheets I just picked a day. Seems the names of the
sheets did not correspond dates used with actual Monday dates. First one was
renamed 09-15-05. The actual Monday should have been 09-19-05.

Once the names were changed to match dates for Mondays the code worked
wonderful.

Thanks for all your help.

Ccrosby

"Tushar Mehta" wrote:

Hopefully, you are OK renaming the worksheets as mm-dd-yy where the
date is the Monday of each week. So, the worksheets would be named 09-
19-05, 10-03-05, etc.

Put this lightly tested code in the code module of ThisWorkbook.

Private Sub Workbook_Open()
Dim CurrMonday As String, i As Integer
CurrMonday = Format(Date - Weekday(Date, vbMonday) + 1, "mm-dd-yy")
With ActiveWorkbook.Worksheets
.Item(CurrMonday).Visible = True
For i = 1 To .Count
With .Item(i)
If IsDate(.Name) Then
.Visible = IIf(CDate(.Name) <= CDate(CurrMonday), _
xlSheetVisible, xlSheetHidden) 'xlsheetveryhidden
Else
.Visible = xlSheetHidden 'xlsheetveryhidden
End If
End With
Next i
End With
End Sub

Whenever the workbook is opened, the code will hide all worksheets
except those whose names represent already passed Mondays.

Worksheets not named in the correct format will be hidden.

The hidden worksheets can be made visible through Format | Sheet
Unhide... To block that capability replace the references to
xlSheetHidden by xlSheetVeryHidden.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have a Excel workbook containing 4 work sheets.

The worksheets contain info for weekly time records.

The time sheets are for a 7 day period.

Therefore I am collecting daily work hours for a 28 day work period on a
weekly basis.

I want employees to be able to open the workbook and only the sheet(s) for
the current time to be active. If possible I wish to hide the other sheets
based on the current date.

An example would be:

Workbook is set up with the following sheets:
Sheet 1 is a weekly time sheet for week of 09/26/2005 through 10/02/2005.
Sheet 2 is a weekly time sheet for week of 10/03/2005 through 10/09/2005.
Sheet 3 is a weekly time sheet for week of 10/10/2005 through 10/16/2005.
Sheet 2 is a weekly time sheet for week of 10/17/2005 through 10/23/2005.

When an employee opens the workbook during peiod of 09/26/2005 through
10/02/2005 I want Sheet 1 to be the only visible sheet.

When an employee opens the workbook during peiod of 10/03/2005 through
10/09/2005 I want both Sheet 1 and Sheet 2 to be the only visible sheets.

When an employee opens the workbook during peiod of 10/10/2005 through
10/16/2005 I want Sheet 1, Sheet 2 and Sheet 3 to be the only visible sheets.

When an employee opens the workbook during period 10/17/2005 through
10/23/2005 I want all 4 sheets to be visible.

I was wanting to use a VBA code to control the visible and hidden behavior
of the individual worksheets based on current date that workbook is accessed.

Any suggestions ?

Thanks





"Tushar Mehta" wrote:

In article ,
says...
Is it possible to use a VBA code to make sheets in an excel workbook active
or hidden based on current date ?

Using Excel 2000

Thanks in advance for any guidance .........


Yes.

For more specific suggestions, you will have to be more specific in
what you want.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

VBA
 

In article ,
says...

When I open the workbook I get:

Run-time error '9':
Subscript out of range

You don't have a worksheet named 09-19-05. If a number (month, date,
or year) is less than 10, a leading zero is required.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Thanks for your response:

I renamed the worksheets to date format and inserted code into
module for ThisWorkbook.

When I open the workbook I get:

Run-time error '9':
Subscript out of range

When I debug the below command is shown in yellow highlight.
.Item(CurrMonday).Visible = True

Any suggestions :)





"Tushar Mehta" wrote:

Hopefully, you are OK renaming the worksheets as mm-dd-yy where the
date is the Monday of each week. So, the worksheets would be named 09-
19-05, 10-03-05, etc.

Put this lightly tested code in the code module of ThisWorkbook.

Private Sub Workbook_Open()
Dim CurrMonday As String, i As Integer
CurrMonday = Format(Date - Weekday(Date, vbMonday) + 1, "mm-dd-yy")
With ActiveWorkbook.Worksheets
.Item(CurrMonday).Visible = True
For i = 1 To .Count
With .Item(i)
If IsDate(.Name) Then
.Visible = IIf(CDate(.Name) <= CDate(CurrMonday), _
xlSheetVisible, xlSheetHidden) 'xlsheetveryhidden
Else
.Visible = xlSheetHidden 'xlsheetveryhidden
End If
End With
Next i
End With
End Sub

Whenever the workbook is opened, the code will hide all worksheets
except those whose names represent already passed Mondays.

Worksheets not named in the correct format will be hidden.

The hidden worksheets can be made visible through Format | Sheet
Unhide... To block that capability replace the references to
xlSheetHidden by xlSheetVeryHidden.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have a Excel workbook containing 4 work sheets.

The worksheets contain info for weekly time records.

The time sheets are for a 7 day period.

Therefore I am collecting daily work hours for a 28 day work period on a
weekly basis.

I want employees to be able to open the workbook and only the sheet(s) for
the current time to be active. If possible I wish to hide the other sheets
based on the current date.

An example would be:

Workbook is set up with the following sheets:
Sheet 1 is a weekly time sheet for week of 09/26/2005 through 10/02/2005.
Sheet 2 is a weekly time sheet for week of 10/03/2005 through 10/09/2005.
Sheet 3 is a weekly time sheet for week of 10/10/2005 through 10/16/2005.
Sheet 2 is a weekly time sheet for week of 10/17/2005 through 10/23/2005.

When an employee opens the workbook during peiod of 09/26/2005 through
10/02/2005 I want Sheet 1 to be the only visible sheet.

When an employee opens the workbook during peiod of 10/03/2005 through
10/09/2005 I want both Sheet 1 and Sheet 2 to be the only visible sheets.

When an employee opens the workbook during peiod of 10/10/2005 through
10/16/2005 I want Sheet 1, Sheet 2 and Sheet 3 to be the only visible sheets.

When an employee opens the workbook during period 10/17/2005 through
10/23/2005 I want all 4 sheets to be visible.

I was wanting to use a VBA code to control the visible and hidden behavior
of the individual worksheets based on current date that workbook is accessed.

Any suggestions ?

Thanks





"Tushar Mehta" wrote:

In article ,
says...
Is it possible to use a VBA code to make sheets in an excel workbook active
or hidden based on current date ?

Using Excel 2000

Thanks in advance for any guidance .........


Yes.

For more specific suggestions, you will have to be more specific in
what you want.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

VBA
 

You are welcome. Glad it worked out.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Correction:

My stupidity

When I renamed the worksheets I just picked a day. Seems the names of the
sheets did not correspond dates used with actual Monday dates. First one was
renamed 09-15-05. The actual Monday should have been 09-19-05.

Once the names were changed to match dates for Mondays the code worked
wonderful.

Thanks for all your help.

Ccrosby

{snip}