Runtime error 1004 Unable to set locked property

[grahamiwa]

I have a sub that I have copied out of a previous workbook where it still works fine. In the previous workbook it was assigned to a button. Now I am trying just to do it as an automatic action when the workbook is closed

It will lock any cell within the specified range that is not blank then protect the worksheet, save the workbook then save a dated archive copy

When I pasted it in to a new workbook I changed the range to A1:AP49 and strFileName

Now when I run it now I get an Error 1004 - Unable to set the Locked property of the Range class.

On Debug this line is highlighted:-
c.Locked = c.Value <> ""



The full code is:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' Lock_cells Macro
' Macro compiled 10/26/2011 by G CARNCROSS
' Locks used cells, saves the workbook then saves a password protected copy with the days date in the file name then closes the workbook
'
'
ActiveSheet.Unprotect Password:="SHES"

Dim c As Range
For Each c In Range("A1:AP49")
c.Locked = c.Value <> ""
Next c

Application.DisplayAlerts = False
ActiveSheet.Protect Password:="SHES", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save

Dim strFileName As String

strFileName = "\\mlbdat02\shared\organization\T&D-All\Training Course Archive \REP Training Schedule" & Chr(32) & Format(Date, "d mmm yyyy") & ".xls"

ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:= _
xlNormal, Password:="SHES", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = True

ActiveWorkbook.Close



End Sub

 

[Jerry Sullivan]

Do you have any merged cells in that range?
A merged cell would cause that error.

 

[grahamiwa]

Great

I did have merged cells in the range which I an not able to change but I have readjusted the renge and it works well now

Thanks