Combining On Open Macros

[Natalie]

Hello All,

I have two macros: 1 to Protect All Sheets and 1 to Hide
sheets based on the month name. (Codes are below)

At the moment the Hide macro runs when the file opens but
the Protection one is run by click a macro button.

Basically I would like to combine the two so that when a
user opens a file it is protected and the hide macro runs.

Any help will be greatfully appreciated!

Natalie

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub



Sub Workbook_Open()

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

Select Case MyMonth

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

[Vasant Nanavati]

Add the following line to the end of your Workbook_Open macro:

Call ProtectAllSheets

or just:

ProtectAllSheets

(I prefer to use the Call terminology because it makes it clear that you are
calling another macro, but it is not required.)

--

Vasant

"Natalie" <anonymous@discussions.microsoft.com> wrote in message
news:57d501c52385$b5f1c370$a401280a@phx.gbl...
> Hello All,
>
> I have two macros: 1 to Protect All Sheets and 1 to Hide
> sheets based on the month name. (Codes are below)
>
> At the moment the Hide macro runs when the file opens but
> the Protection one is run by click a macro button.
>
> Basically I would like to combine the two so that when a
> user opens a file it is protected and the hide macro runs.
>
> Any help will be greatfully appreciated!
>
> Natalie
>
> Sub ProtectAllSheets()
> Application.ScreenUpdating = False
> Dim n As Single
> For n = 1 To Sheets.Count
> Sheets(n).Protect Password:="justme"
> Next n
> Application.ScreenUpdating = True
> End Sub
>
>
>
> Sub Workbook_Open()
>
> Dim MyMonth As Integer
> MyMonth = Month(Now())
>
> Select Case MyMonth
>
> Case 1 'If month number is 1 (Jan)
> Sheets("Feb").Visible = False
> Sheets("Mar").Visible = False
> Case 2 'If Month number is 2 (Feb)
> Sheets("Jan").Visible = False
> Sheets("March").Visible = False
> Case 3 'If Month number is 3 (Mar)
> Sheets("Jan").Visible = False
> Sheets("Mar").Visible = True
> Sheets("Feb").Visible = False
> End Select
> End Sub
>
>

[Natalie]

Cheers!!
>-----Original Message-----
>Add the following line to the end of your Workbook_Open
macro:
>
>Call ProtectAllSheets
>
>or just:
>
>ProtectAllSheets
>
>(I prefer to use the Call terminology because it makes
it clear that you are
>calling another macro, but it is not required.)
>
>--
>
>Vasant
>
>"Natalie" <anonymous@discussions.microsoft.com> wrote in
message
>news:57d501c52385$b5f1c370$a401280a@phx.gbl...
>> Hello All,
>>
>> I have two macros: 1 to Protect All Sheets and 1 to
Hide
>> sheets based on the month name. (Codes are below)
>>
>> At the moment the Hide macro runs when the file opens
but
>> the Protection one is run by click a macro button.
>>
>> Basically I would like to combine the two so that when
a
>> user opens a file it is protected and the hide macro
runs.
>>
>> Any help will be greatfully appreciated!
>>
>> Natalie
>>
>> Sub ProtectAllSheets()
>> Application.ScreenUpdating = False
>> Dim n As Single
>> For n = 1 To Sheets.Count
>> Sheets(n).Protect Password:="justme"
>> Next n
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>>
>> Sub Workbook_Open()
>>
>> Dim MyMonth As Integer
>> MyMonth = Month(Now())
>>
>> Select Case MyMonth
>>
>> Case 1 'If month number is 1 (Jan)
>> Sheets("Feb").Visible = False
>> Sheets("Mar").Visible = False
>> Case 2 'If Month number is 2 (Feb)
>> Sheets("Jan").Visible = False
>> Sheets("March").Visible = False
>> Case 3 'If Month number is 3 (Mar)
>> Sheets("Jan").Visible = False
>> Sheets("Mar").Visible = True
>> Sheets("Feb").Visible = False
>> End Select
>> End Sub
>>
>>
>
>
>.
>

[Dana DeLouis]

Would any ideas here help?

Sub Workbook_Open()
Dim MyMonth As Long
MyMonth = Month(Now())

Sheets("Jan").Visible = MyMonth = 1
Sheets("Feb").Visible = MyMonth = 2
Sheets("Mar").Visible = MyMonth = 3
End Sub

HTH
--
Dana DeLouis
Win XP & Office 2003


"Natalie" <anonymous@discussions.microsoft.com> wrote in message
news:582d01c52391$009ebbc0$a401280a@phx.gbl...
> Cheers!!
>>-----Original Message-----
>>Add the following line to the end of your Workbook_Open
> macro:
>>
>>Call ProtectAllSheets
>>
>>or just:
>>
>>ProtectAllSheets
>>
>>(I prefer to use the Call terminology because it makes
> it clear that you are
>>calling another macro, but it is not required.)
>>
>>--
>>
>>Vasant
>>
>>"Natalie" <anonymous@discussions.microsoft.com> wrote in
> message
>>news:57d501c52385$b5f1c370$a401280a@phx.gbl...
>>> Hello All,
>>>
>>> I have two macros: 1 to Protect All Sheets and 1 to
> Hide
>>> sheets based on the month name. (Codes are below)
>>>
>>> At the moment the Hide macro runs when the file opens
> but
>>> the Protection one is run by click a macro button.
>>>
>>> Basically I would like to combine the two so that when
> a
>>> user opens a file it is protected and the hide macro
> runs.
>>>
>>> Any help will be greatfully appreciated!
>>>
>>> Natalie
>>>
>>> Sub ProtectAllSheets()
>>> Application.ScreenUpdating = False
>>> Dim n As Single
>>> For n = 1 To Sheets.Count
>>> Sheets(n).Protect Password:="justme"
>>> Next n
>>> Application.ScreenUpdating = True
>>> End Sub
>>>
>>>
>>>
>>> Sub Workbook_Open()
>>>
>>> Dim MyMonth As Integer
>>> MyMonth = Month(Now())
>>>
>>> Select Case MyMonth
>>>
>>> Case 1 'If month number is 1 (Jan)
>>> Sheets("Feb").Visible = False
>>> Sheets("Mar").Visible = False
>>> Case 2 'If Month number is 2 (Feb)
>>> Sheets("Jan").Visible = False
>>> Sheets("March").Visible = False
>>> Case 3 'If Month number is 3 (Mar)
>>> Sheets("Jan").Visible = False
>>> Sheets("Mar").Visible = True
>>> Sheets("Feb").Visible = False
>>> End Select
>>> End Sub
>>>
>>>
>>
>>
>>.
>>

[Bob Phillips]

Problem.

On 1st Feb, when first opened, Jan will be visible, Feb will not. As soon as
it executes the line

Sheets("Jan").Visible = MyMonth = 1

it will fail as it is trying to hide the last visible sheet. You probably
only need

Sub Workbook_Open()
Dim iThisMonth As Long
Dim iLastMonth As Long
Dim sThisMonth As String
Dim sLastMonth As String

iThisMonth = Month(Date)
iLastMonth = iThisMonth - 1
If iLastMonth = 0 Then iLastMonth = 12

sThisMonth = Format(DateValue(Year(Date) & "-" & iThisMonth & "-01"),
"mmm")
Worksheets(sThisMonth).Visible = True

sLastMonth = Format(DateValue(Year(Date) & "-" & iLastMonth & "-01"),
"mmm")
Worksheets(sLastMonth).Visible = False

End Sub


Of course assuming that the workbook is properly setup manually.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:evpzKp5IFHA.3196@TK2MSFTNGP15.phx.gbl...
> Would any ideas here help?
>
> Sub Workbook_Open()
> Dim MyMonth As Long
> MyMonth = Month(Now())
>
> Sheets("Jan").Visible = MyMonth = 1
> Sheets("Feb").Visible = MyMonth = 2
> Sheets("Mar").Visible = MyMonth = 3
> End Sub
>
> HTH
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Natalie" <anonymous@discussions.microsoft.com> wrote in message
> news:582d01c52391$009ebbc0$a401280a@phx.gbl...
> > Cheers!!
> >>-----Original Message-----
> >>Add the following line to the end of your Workbook_Open
> > macro:
> >>
> >>Call ProtectAllSheets
> >>
> >>or just:
> >>
> >>ProtectAllSheets
> >>
> >>(I prefer to use the Call terminology because it makes
> > it clear that you are
> >>calling another macro, but it is not required.)
> >>
> >>--
> >>
> >>Vasant
> >>
> >>"Natalie" <anonymous@discussions.microsoft.com> wrote in
> > message
> >>news:57d501c52385$b5f1c370$a401280a@phx.gbl...
> >>> Hello All,
> >>>
> >>> I have two macros: 1 to Protect All Sheets and 1 to
> > Hide
> >>> sheets based on the month name. (Codes are below)
> >>>
> >>> At the moment the Hide macro runs when the file opens
> > but
> >>> the Protection one is run by click a macro button.
> >>>
> >>> Basically I would like to combine the two so that when
> > a
> >>> user opens a file it is protected and the hide macro
> > runs.
> >>>
> >>> Any help will be greatfully appreciated!
> >>>
> >>> Natalie
> >>>
> >>> Sub ProtectAllSheets()
> >>> Application.ScreenUpdating = False
> >>> Dim n As Single
> >>> For n = 1 To Sheets.Count
> >>> Sheets(n).Protect Password:="justme"
> >>> Next n
> >>> Application.ScreenUpdating = True
> >>> End Sub
> >>>
> >>>
> >>>
> >>> Sub Workbook_Open()
> >>>
> >>> Dim MyMonth As Integer
> >>> MyMonth = Month(Now())
> >>>
> >>> Select Case MyMonth
> >>>
> >>> Case 1 'If month number is 1 (Jan)
> >>> Sheets("Feb").Visible = False
> >>> Sheets("Mar").Visible = False
> >>> Case 2 'If Month number is 2 (Feb)
> >>> Sheets("Jan").Visible = False
> >>> Sheets("March").Visible = False
> >>> Case 3 'If Month number is 3 (Mar)
> >>> Sheets("Jan").Visible = False
> >>> Sheets("Mar").Visible = True
> >>> Sheets("Feb").Visible = False
> >>> End Select
> >>> End Sub
> >>>
> >>>
> >>
> >>
> >>.
> >>
>
>

[Dana DeLouis]

Thanks Bob! Didn't think about that. I like your code. How about this
small idea?
iLastMonth = ((MyMonth + 10) Mod 12) + 1

If the op has only 3 sheets, and I want to try to salvage my code, perhaps
this small change then.
Select Case MyMonth
Case 1 To 3
Sheets("Jan").Visible = True ' Visible for now
Sheets("Feb").Visible = MyMonth = 2
Sheets("Mar").Visible = MyMonth = 3
Sheets("Jan").Visible = MyMonth = 1 ' Do Jan
Case Else
' Not sure...
End Select

Again, just throwing out some general ideas.
--
Dana DeLouis
Win XP & Office 2003


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:Oe70wZ8IFHA.3528@tk2msftngp13.phx.gbl...
> Problem.
>
> On 1st Feb, when first opened, Jan will be visible, Feb will not. As soon
> as
> it executes the line
>
> Sheets("Jan").Visible = MyMonth = 1
>
> it will fail as it is trying to hide the last visible sheet. You probably
> only need
>
> Sub Workbook_Open()
> Dim iThisMonth As Long
> Dim iLastMonth As Long
> Dim sThisMonth As String
> Dim sLastMonth As String
>
> iThisMonth = Month(Date)
> iLastMonth = iThisMonth - 1
> If iLastMonth = 0 Then iLastMonth = 12
>
> sThisMonth = Format(DateValue(Year(Date) & "-" & iThisMonth & "-01"),
> "mmm")
> Worksheets(sThisMonth).Visible = True
>
> sLastMonth = Format(DateValue(Year(Date) & "-" & iLastMonth & "-01"),
> "mmm")
> Worksheets(sLastMonth).Visible = False
>
> End Sub
>
>
> Of course assuming that the workbook is properly setup manually.
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Dana DeLouis" <delouis@bellsouth.net> wrote in message
> news:evpzKp5IFHA.3196@TK2MSFTNGP15.phx.gbl...
>> Would any ideas here help?
>>
>> Sub Workbook_Open()
>> Dim MyMonth As Long
>> MyMonth = Month(Now())
>>
>> Sheets("Jan").Visible = MyMonth = 1
>> Sheets("Feb").Visible = MyMonth = 2
>> Sheets("Mar").Visible = MyMonth = 3
>> End Sub
>>
>> HTH
>> --
>> Dana DeLouis
>> Win XP & Office 2003
>>
>>
>> "Natalie" <anonymous@discussions.microsoft.com> wrote in message
>> news:582d01c52391$009ebbc0$a401280a@phx.gbl...
>> > Cheers!!
>> >>-----Original Message-----
>> >>Add the following line to the end of your Workbook_Open
>> > macro:
>> >>
>> >>Call ProtectAllSheets
>> >>
>> >>or just:
>> >>
>> >>ProtectAllSheets
>> >>
>> >>(I prefer to use the Call terminology because it makes
>> > it clear that you are
>> >>calling another macro, but it is not required.)
>> >>
>> >>--
>> >>
>> >>Vasant
>> >>
>> >>"Natalie" <anonymous@discussions.microsoft.com> wrote in
>> > message
>> >>news:57d501c52385$b5f1c370$a401280a@phx.gbl...
>> >>> Hello All,
>> >>>
>> >>> I have two macros: 1 to Protect All Sheets and 1 to
>> > Hide
>> >>> sheets based on the month name. (Codes are below)
>> >>>
>> >>> At the moment the Hide macro runs when the file opens
>> > but
>> >>> the Protection one is run by click a macro button.
>> >>>
>> >>> Basically I would like to combine the two so that when
>> > a
>> >>> user opens a file it is protected and the hide macro
>> > runs.
>> >>>
>> >>> Any help will be greatfully appreciated!
>> >>>
>> >>> Natalie
>> >>>
>> >>> Sub ProtectAllSheets()
>> >>> Application.ScreenUpdating = False
>> >>> Dim n As Single
>> >>> For n = 1 To Sheets.Count
>> >>> Sheets(n).Protect Password:="justme"
>> >>> Next n
>> >>> Application.ScreenUpdating = True
>> >>> End Sub
>> >>>
>> >>>
>> >>>
>> >>> Sub Workbook_Open()
>> >>>
>> >>> Dim MyMonth As Integer
>> >>> MyMonth = Month(Now())
>> >>>
>> >>> Select Case MyMonth
>> >>>
>> >>> Case 1 'If month number is 1 (Jan)
>> >>> Sheets("Feb").Visible = False
>> >>> Sheets("Mar").Visible = False
>> >>> Case 2 'If Month number is 2 (Feb)
>> >>> Sheets("Jan").Visible = False
>> >>> Sheets("March").Visible = False
>> >>> Case 3 'If Month number is 3 (Mar)
>> >>> Sheets("Jan").Visible = False
>> >>> Sheets("Mar").Visible = True
>> >>> Sheets("Feb").Visible = False
>> >>> End Select
>> >>> End Sub
>> >>>
>> >>>
>> >>
>> >>
>> >>.
>> >>
>>
>>
>
>

[Bob Phillips]

Hi Dana,

Yeah, that is good, a well-honed type of technique from you :-). I am an
old-fashioned developer, If ... Then ... Else ... End If is my normal
structure, whereas I think that you very much prefer shortcuts, but I admit
to preferring this. I also like using a condition to set a property (like
your Sheets("Feb").Visible = MyMonth = 2), use it a lot myself.

I think the OP has twelve sheets. The bit you may not be aware of is a
previous post where she mentioned this, and Jim Thomlinson gave her a
solution (which is not what she is using here!).

Regards

Bob

"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:%23ukrQD%23IFHA.3916@TK2MSFTNGP14.phx.gbl...
> Thanks Bob! Didn't think about that. I like your code. How about this
> small idea?
> iLastMonth = ((MyMonth + 10) Mod 12) + 1
>
> If the op has only 3 sheets, and I want to try to salvage my code,
perhaps
> this small change then.
> Select Case MyMonth
> Case 1 To 3
> Sheets("Jan").Visible = True ' Visible for now
> Sheets("Feb").Visible = MyMonth = 2
> Sheets("Mar").Visible = MyMonth = 3
> Sheets("Jan").Visible = MyMonth = 1 ' Do Jan
> Case Else
> ' Not sure...
> End Select
>
> Again, just throwing out some general ideas.
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:Oe70wZ8IFHA.3528@tk2msftngp13.phx.gbl...
> > Problem.
> >
> > On 1st Feb, when first opened, Jan will be visible, Feb will not. As
soon
> > as
> > it executes the line
> >
> > Sheets("Jan").Visible = MyMonth = 1
> >
> > it will fail as it is trying to hide the last visible sheet. You
probably
> > only need
> >
> > Sub Workbook_Open()
> > Dim iThisMonth As Long
> > Dim iLastMonth As Long
> > Dim sThisMonth As String
> > Dim sLastMonth As String
> >
> > iThisMonth = Month(Date)
> > iLastMonth = iThisMonth - 1
> > If iLastMonth = 0 Then iLastMonth = 12
> >
> > sThisMonth = Format(DateValue(Year(Date) & "-" & iThisMonth & "-01"),
> > "mmm")
> > Worksheets(sThisMonth).Visible = True
> >
> > sLastMonth = Format(DateValue(Year(Date) & "-" & iLastMonth & "-01"),
> > "mmm")
> > Worksheets(sLastMonth).Visible = False
> >
> > End Sub
> >
> >
> > Of course assuming that the workbook is properly setup manually.
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Dana DeLouis" <delouis@bellsouth.net> wrote in message
> > news:evpzKp5IFHA.3196@TK2MSFTNGP15.phx.gbl...
> >> Would any ideas here help?
> >>
> >> Sub Workbook_Open()
> >> Dim MyMonth As Long
> >> MyMonth = Month(Now())
> >>
> >> Sheets("Jan").Visible = MyMonth = 1
> >> Sheets("Feb").Visible = MyMonth = 2
> >> Sheets("Mar").Visible = MyMonth = 3
> >> End Sub
> >>
> >> HTH
> >> --
> >> Dana DeLouis
> >> Win XP & Office 2003
> >>
> >>
> >> "Natalie" <anonymous@discussions.microsoft.com> wrote in message
> >> news:582d01c52391$009ebbc0$a401280a@phx.gbl...
> >> > Cheers!!
> >> >>-----Original Message-----
> >> >>Add the following line to the end of your Workbook_Open
> >> > macro:
> >> >>
> >> >>Call ProtectAllSheets
> >> >>
> >> >>or just:
> >> >>
> >> >>ProtectAllSheets
> >> >>
> >> >>(I prefer to use the Call terminology because it makes
> >> > it clear that you are
> >> >>calling another macro, but it is not required.)
> >> >>
> >> >>--
> >> >>
> >> >>Vasant
> >> >>
> >> >>"Natalie" <anonymous@discussions.microsoft.com> wrote in
> >> > message
> >> >>news:57d501c52385$b5f1c370$a401280a@phx.gbl...
> >> >>> Hello All,
> >> >>>
> >> >>> I have two macros: 1 to Protect All Sheets and 1 to
> >> > Hide
> >> >>> sheets based on the month name. (Codes are below)
> >> >>>
> >> >>> At the moment the Hide macro runs when the file opens
> >> > but
> >> >>> the Protection one is run by click a macro button.
> >> >>>
> >> >>> Basically I would like to combine the two so that when
> >> > a
> >> >>> user opens a file it is protected and the hide macro
> >> > runs.
> >> >>>
> >> >>> Any help will be greatfully appreciated!
> >> >>>
> >> >>> Natalie
> >> >>>
> >> >>> Sub ProtectAllSheets()
> >> >>> Application.ScreenUpdating = False
> >> >>> Dim n As Single
> >> >>> For n = 1 To Sheets.Count
> >> >>> Sheets(n).Protect Password:="justme"
> >> >>> Next n
> >> >>> Application.ScreenUpdating = True
> >> >>> End Sub
> >> >>>
> >> >>>
> >> >>>
> >> >>> Sub Workbook_Open()
> >> >>>
> >> >>> Dim MyMonth As Integer
> >> >>> MyMonth = Month(Now())
> >> >>>
> >> >>> Select Case MyMonth
> >> >>>
> >> >>> Case 1 'If month number is 1 (Jan)
> >> >>> Sheets("Feb").Visible = False
> >> >>> Sheets("Mar").Visible = False
> >> >>> Case 2 'If Month number is 2 (Feb)
> >> >>> Sheets("Jan").Visible = False
> >> >>> Sheets("March").Visible = False
> >> >>> Case 3 'If Month number is 3 (Mar)
> >> >>> Sheets("Jan").Visible = False
> >> >>> Sheets("Mar").Visible = True
> >> >>> Sheets("Feb").Visible = False
> >> >>> End Select
> >> >>> End Sub
> >> >>>
> >> >>>
> >> >>
> >> >>
> >> >>.
> >> >>
> >>
> >>
> >
> >
>
>

[Dana DeLouis]

Thanks Bob. Didn't see that other thread. Just to throw out another
general idea with 12 sheets, and a later version of Excel ( for "MonthName"
function)

Sub Workbook_Open()
Dim MyMonth As Long
Dim Mth As Long

MyMonth = Month(Now())

'Keep Jan visible for now
'Note: MonthName(1, True) -> "Jan"
Sheets(MonthName(1, True)).Visible = True
For Mth = 2 To 12
Sheets(MonthName(Mth, True)).Visible = (MyMonth = Mth)
Next Mth
'Now do Jan
Sheets(MonthName(1, True)).Visible = (MyMonth = 1)
End Sub

--
Dana DeLouis
Win XP & Office 2003


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uK$teM%23IFHA.580@TK2MSFTNGP15.phx.gbl...
> Hi Dana,
>
> Yeah, that is good, a well-honed type of technique from you :-). I am an
> old-fashioned developer, If ... Then ... Else ... End If is my normal
> structure, whereas I think that you very much prefer shortcuts, but I
> admit
> to preferring this. I also like using a condition to set a property (like
> your Sheets("Feb").Visible = MyMonth = 2), use it a lot myself.
>
> I think the OP has twelve sheets. The bit you may not be aware of is a
> previous post where she mentioned this, and Jim Thomlinson gave her a
> solution (which is not what she is using here!).
>
> Regards
>
> Bob
>
> "Dana DeLouis" <delouis@bellsouth.net> wrote in message
> news:%23ukrQD%23IFHA.3916@TK2MSFTNGP14.phx.gbl...
>> Thanks Bob! Didn't think about that. I like your code. How about this
>> small idea?
>> iLastMonth = ((MyMonth + 10) Mod 12) + 1
>>
>> If the op has only 3 sheets, and I want to try to salvage my code,
> perhaps
>> this small change then.
>> Select Case MyMonth
>> Case 1 To 3
>> Sheets("Jan").Visible = True ' Visible for now
>> Sheets("Feb").Visible = MyMonth = 2
>> Sheets("Mar").Visible = MyMonth = 3
>> Sheets("Jan").Visible = MyMonth = 1 ' Do Jan
>> Case Else
>> ' Not sure...
>> End Select
>>
>> Again, just throwing out some general ideas.
>> --
>> Dana DeLouis
>> Win XP & Office 2003
>>
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:Oe70wZ8IFHA.3528@tk2msftngp13.phx.gbl...
>> > Problem.
>> >
>> > On 1st Feb, when first opened, Jan will be visible, Feb will not. As
> soon
>> > as
>> > it executes the line
>> >
>> > Sheets("Jan").Visible = MyMonth = 1
>> >
>> > it will fail as it is trying to hide the last visible sheet. You
> probably
>> > only need
>> >
>> > Sub Workbook_Open()
>> > Dim iThisMonth As Long
>> > Dim iLastMonth As Long
>> > Dim sThisMonth As String
>> > Dim sLastMonth As String
>> >
>> > iThisMonth = Month(Date)
>> > iLastMonth = iThisMonth - 1
>> > If iLastMonth = 0 Then iLastMonth = 12
>> >
>> > sThisMonth = Format(DateValue(Year(Date) & "-" & iThisMonth &
>> > "-01"),
>> > "mmm")
>> > Worksheets(sThisMonth).Visible = True
>> >
>> > sLastMonth = Format(DateValue(Year(Date) & "-" & iLastMonth &
>> > "-01"),
>> > "mmm")
>> > Worksheets(sLastMonth).Visible = False
>> >
>> > End Sub
>> >
>> >
>> > Of course assuming that the workbook is properly setup manually.
>> >
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Dana DeLouis" <delouis@bellsouth.net> wrote in message
>> > news:evpzKp5IFHA.3196@TK2MSFTNGP15.phx.gbl...
>> >> Would any ideas here help?
>> >>
>> >> Sub Workbook_Open()
>> >> Dim MyMonth As Long
>> >> MyMonth = Month(Now())
>> >>
>> >> Sheets("Jan").Visible = MyMonth = 1
>> >> Sheets("Feb").Visible = MyMonth = 2
>> >> Sheets("Mar").Visible = MyMonth = 3
>> >> End Sub
>> >>
>> >> HTH
>> >> --
>> >> Dana DeLouis
>> >> Win XP & Office 2003
>> >>
>> >>
>> >> "Natalie" <anonymous@discussions.microsoft.com> wrote in message
>> >> news:582d01c52391$009ebbc0$a401280a@phx.gbl...
>> >> > Cheers!!
>> >> >>-----Original Message-----
>> >> >>Add the following line to the end of your Workbook_Open
>> >> > macro:
>> >> >>
>> >> >>Call ProtectAllSheets
>> >> >>
>> >> >>or just:
>> >> >>
>> >> >>ProtectAllSheets
>> >> >>
>> >> >>(I prefer to use the Call terminology because it makes
>> >> > it clear that you are
>> >> >>calling another macro, but it is not required.)
>> >> >>
>> >> >>--
>> >> >>
>> >> >>Vasant
>> >> >>
>> >> >>"Natalie" <anonymous@discussions.microsoft.com> wrote in
>> >> > message
>> >> >>news:57d501c52385$b5f1c370$a401280a@phx.gbl...
>> >> >>> Hello All,
>> >> >>>
>> >> >>> I have two macros: 1 to Protect All Sheets and 1 to
>> >> > Hide
>> >> >>> sheets based on the month name. (Codes are below)
>> >> >>>
>> >> >>> At the moment the Hide macro runs when the file opens
>> >> > but
>> >> >>> the Protection one is run by click a macro button.
>> >> >>>
>> >> >>> Basically I would like to combine the two so that when
>> >> > a
>> >> >>> user opens a file it is protected and the hide macro
>> >> > runs.
>> >> >>>
>> >> >>> Any help will be greatfully appreciated!
>> >> >>>
>> >> >>> Natalie
>> >> >>>
>> >> >>> Sub ProtectAllSheets()
>> >> >>> Application.ScreenUpdating = False
>> >> >>> Dim n As Single
>> >> >>> For n = 1 To Sheets.Count
>> >> >>> Sheets(n).Protect Password:="justme"
>> >> >>> Next n
>> >> >>> Application.ScreenUpdating = True
>> >> >>> End Sub
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >>> Sub Workbook_Open()
>> >> >>>
>> >> >>> Dim MyMonth As Integer
>> >> >>> MyMonth = Month(Now())
>> >> >>>
>> >> >>> Select Case MyMonth
>> >> >>>
>> >> >>> Case 1 'If month number is 1 (Jan)
>> >> >>> Sheets("Feb").Visible = False
>> >> >>> Sheets("Mar").Visible = False
>> >> >>> Case 2 'If Month number is 2 (Feb)
>> >> >>> Sheets("Jan").Visible = False
>> >> >>> Sheets("March").Visible = False
>> >> >>> Case 3 'If Month number is 3 (Mar)
>> >> >>> Sheets("Jan").Visible = False
>> >> >>> Sheets("Mar").Visible = True
>> >> >>> Sheets("Feb").Visible = False
>> >> >>> End Select
>> >> >>> End Sub
>> >> >>>
>> >> >>>
>> >> >>
>> >> >>
>> >> >>.
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Tushar Mehta]

Why not play safe? Defensively written code:

Sub testIt2()
Dim ThisMth As String, aWS As Worksheet
ThisMth = Format(Now(), "mmm")
With ActiveWorkbook
.Worksheets(ThisMth).Visible = True
For Each aWS In .Worksheets
aWS.Visible = aWS.Name = ThisMth
Next aWS
End With
End Sub

--
Regards,

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

In article <#ukrQD#IFHA.3916@TK2MSFTNGP14.phx.gbl>,
delouis@bellsouth.net says...
> Thanks Bob! Didn't think about that. I like your code. How about this
> small idea?
> iLastMonth = ((MyMonth + 10) Mod 12) + 1
>
> If the op has only 3 sheets, and I want to try to salvage my code, perhaps
> this small change then.
> Select Case MyMonth
> Case 1 To 3
> Sheets("Jan").Visible = True ' Visible for now
> Sheets("Feb").Visible = MyMonth = 2
> Sheets("Mar").Visible = MyMonth = 3
> Sheets("Jan").Visible = MyMonth = 1 ' Do Jan
> Case Else
> ' Not sure...
> End Select
>
> Again, just throwing out some general ideas.
>