Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Hi I have the following Macro in mt excel workbook.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Call ProtectAllSheets Call PageSetUp End Sub Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="1257" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="1257" Next n Application.ScreenUpdating = True End Sub Sub PageSetUp() Dim SH1 As Object Dim SH2 As Object With ActiveWorkbook Set SH1 = .Sheets("Relief Final Shifts") Set SH2 = .Sheets("Shifts still to Cover") End With SH1.PageSetUp.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") SH2.PageSetUp.RightFooter = Format(Now, "dd-mmm-yy") End Sub this was working fine until I saved the excel file to a new name using save as, now this original template file will not operate the macro UnProtectWorkbook properly, when I go to view macro and run the macro in coming up with the excel workbook name first and is unable to run macro until I press alt + f11 then save it again in vba, this works until I save the workbook then i'm back to the beginning fault, anyone have any ideas what might be wrong and how to rectify this. Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Sorry Try again.
When I go into VBA and save the macro is fine until I exit the excel file and reopen it this is when the macro is back to 'excel file name'!UnProtectAllSheets "santaviga" wrote: Hi I have the following Macro in mt excel workbook. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Call ProtectAllSheets Call PageSetUp End Sub Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="1257" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="1257" Next n Application.ScreenUpdating = True End Sub Sub PageSetUp() Dim SH1 As Object Dim SH2 As Object With ActiveWorkbook Set SH1 = .Sheets("Relief Final Shifts") Set SH2 = .Sheets("Shifts still to Cover") End With SH1.PageSetUp.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") SH2.PageSetUp.RightFooter = Format(Now, "dd-mmm-yy") End Sub this was working fine until I saved the excel file to a new name using save as, now this original template file will not operate the macro UnProtectWorkbook properly, when I go to view macro and run the macro in coming up with the excel workbook name first and is unable to run macro until I press alt + f11 then save it again in vba, this works until I save the workbook then i'm back to the beginning fault, anyone have any ideas what might be wrong and how to rectify this. Many thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Hi Santaviga,
It happens to me once and the problem was that the extension of the file was xls instead of xlsm (macro enable), I just have to save the file with that extenxion and it worked "santaviga" wrote: Sorry Try again. When I go into VBA and save the macro is fine until I exit the excel file and reopen it this is when the macro is back to 'excel file name'!UnProtectAllSheets "santaviga" wrote: Hi I have the following Macro in mt excel workbook. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Call ProtectAllSheets Call PageSetUp End Sub Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="1257" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="1257" Next n Application.ScreenUpdating = True End Sub Sub PageSetUp() Dim SH1 As Object Dim SH2 As Object With ActiveWorkbook Set SH1 = .Sheets("Relief Final Shifts") Set SH2 = .Sheets("Shifts still to Cover") End With SH1.PageSetUp.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") SH2.PageSetUp.RightFooter = Format(Now, "dd-mmm-yy") End Sub this was working fine until I saved the excel file to a new name using save as, now this original template file will not operate the macro UnProtectWorkbook properly, when I go to view macro and run the macro in coming up with the excel workbook name first and is unable to run macro until I press alt + f11 then save it again in vba, this works until I save the workbook then i'm back to the beginning fault, anyone have any ideas what might be wrong and how to rectify this. Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |