Hello there,

I have a large number of spreadsheets each with 12 sheets
named after the abbreviations of months.

I only want my users to be able to see the relevant
month's sheet, ie. if in January hide all the other month
sheets. OR. At least protect the other sheets so they
cannot edit the other sheets figures.

All the spreadsheets will be saved into the same shared
folder.

I'm thinking that maybe there a way of doing this each
month for the multiple workbooks, or have a macro the
runs automatically each month?????

Any input in this will be VERY gratefully received!!

Cheers

Judith

Judith,

If you're familiar with VBa then the following piece of code will be fairly
easy to understand, and will work for you.

Dim MyMonth As Integer
MyMonth = Month(Now())


Select Case Month

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
End Select

What this does is find the current month number, and depending on its
condition, hides unwanted sheets.
The best place for this code is in the 'Open Workbook' event, so that it
executes everytime the workbook is opened.
The code is an abridged version of the full code required, it obviously
needs repeating until you have 12 different case statements, each hiding 11
worksheets

Also, I would put some code in the 'Close Workbook' event which made all 12
sheets visible again e.g.

Sheets("Jan").Visible = True

Repeated for all months.


Hope this makes sense, if you are unsure of how to go about this in VBA
please re-post or contact me directly and I will be glad to be of assistance.

Neil
www.nwarwick.co.uk
"JudithJubilee" wrote:

Hello there,

I have a large number of spreadsheets each with 12 sheets
named after the abbreviations of months.

I only want my users to be able to see the relevant
month's sheet, ie. if in January hide all the other month
sheets. OR. At least protect the other sheets so they
cannot edit the other sheets figures.

All the spreadsheets will be saved into the same shared
folder.

I'm thinking that maybe there a way of doing this each
month for the multiple workbooks, or have a macro the
runs automatically each month?????

Any input in this will be VERY gratefully received!!

Cheers

Judith

Hi

Somewhat compacter version of code, which does the same:
....
Dim MyMonth As Integer)
MyMonth = Month(Now())
Sheets("Jan").Visible=Iif(MyMonth=1,True,False)
Sheets("Feb").Visible=Iif(MyMonth=2,True,False)
....
Sheets("Dec").Visible=Iif(MyMonth=12,True,False)
......

I'm not sure - maybe this will also work:
....
Sheets("Jan").Visible=(MyMonth=1)
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Neil" wrote in message
...
Judith,

If you're familiar with VBa then the following piece of code will be
fairly
easy to understand, and will work for you.

Dim MyMonth As Integer
MyMonth = Month(Now())


Select Case Month

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
End Select

What this does is find the current month number, and depending on its
condition, hides unwanted sheets.
The best place for this code is in the 'Open Workbook' event, so that it
executes everytime the workbook is opened.
The code is an abridged version of the full code required, it obviously
needs repeating until you have 12 different case statements, each hiding
11
worksheets

Also, I would put some code in the 'Close Workbook' event which made all
12
sheets visible again e.g.

Sheets("Jan").Visible = True

Repeated for all months.


Hope this makes sense, if you are unsure of how to go about this in VBA
please re-post or contact me directly and I will be glad to be of
assistance.

Neil
www.nwarwick.co.uk
"JudithJubilee" wrote:

Hello there,

I have a large number of spreadsheets each with 12 sheets
named after the abbreviations of months.

I only want my users to be able to see the relevant
month's sheet, ie. if in January hide all the other month
sheets. OR. At least protect the other sheets so they
cannot edit the other sheets figures.

All the spreadsheets will be saved into the same shared
folder.

I'm thinking that maybe there a way of doing this each
month for the multiple workbooks, or have a macro the
runs automatically each month?????

Any input in this will be VERY gratefully received!!

Cheers

Judith

Sorry Judith,

Slight error in my code, should have re-read it before I posted (It is
5.30am in U.K.!)

The 3rd line of code should read:

Select Case MyMonth

Neil

"Neil" wrote:

Judith,

If you're familiar with VBa then the following piece of code will be fairly
easy to understand, and will work for you.

Dim MyMonth As Integer
MyMonth = Month(Now())


Select Case Month

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
End Select

What this does is find the current month number, and depending on its
condition, hides unwanted sheets.
The best place for this code is in the 'Open Workbook' event, so that it
executes everytime the workbook is opened.
The code is an abridged version of the full code required, it obviously
needs repeating until you have 12 different case statements, each hiding 11
worksheets

Also, I would put some code in the 'Close Workbook' event which made all 12
sheets visible again e.g.

Sheets("Jan").Visible = True

Repeated for all months.


Hope this makes sense, if you are unsure of how to go about this in VBA
please re-post or contact me directly and I will be glad to be of assistance.

Neil
www.nwarwick.co.uk
"JudithJubilee" wrote:

Hello there,

I have a large number of spreadsheets each with 12 sheets
named after the abbreviations of months.

I only want my users to be able to see the relevant
month's sheet, ie. if in January hide all the other month
sheets. OR. At least protect the other sheets so they
cannot edit the other sheets figures.

All the spreadsheets will be saved into the same shared
folder.

I'm thinking that maybe there a way of doing this each
month for the multiple workbooks, or have a macro the
runs automatically each month?????

Any input in this will be VERY gratefully received!!

Cheers

Judith

And one more...

Option Explicit
Sub auto_open()

Dim iCtr As Long
Dim CurMonthName As String

CurMonthName = Format(Date, "mmm")

On Error Resume Next 'just in case of naming problems
'show the current month
Worksheets(CurMonthName).Visible = True
For iCtr = 1 To Worksheets.Count
If LCase(Worksheets(iCtr).Name) = LCase(CurMonthName) Then
'do nothing--it's already shown
Else
Worksheets(iCtr).Visible = False
End If
Next iCtr
On Error GoTo 0

End Sub

JudithJubilee wrote:

Hello there,

I have a large number of spreadsheets each with 12 sheets
named after the abbreviations of months.

I only want my users to be able to see the relevant
month's sheet, ie. if in January hide all the other month
sheets. OR. At least protect the other sheets so they
cannot edit the other sheets figures.

All the spreadsheets will be saved into the same shared
folder.

I'm thinking that maybe there a way of doing this each
month for the multiple workbooks, or have a macro the
runs automatically each month?????

Any input in this will be VERY gratefully received!!

Cheers

Judith

--

Dave Peterson