Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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" wrote in message ... 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining On Open Macros
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining two macros | Excel Discussion (Misc queries) | |||
Combining Macros and if function | Excel Worksheet Functions | |||
Combining macros | Excel Discussion (Misc queries) | |||
Combining macros | Excel Discussion (Misc queries) | |||
Combining 3 macros to 1 | Excel Programming |