Macro Help

[santaviga]

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

 

[santaviga]

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

 

[Eduardo]

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