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

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

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