| 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 . |
| 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 | |||
![Natalie[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
