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.)

 

[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




.

 

[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

 

[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]

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.

 

[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]

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

 

[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