Protect - Unprotect code location

[usmc-r70]

I have the following code that works except for the ActiveSheet.Unprotect and
ActiveSheet.Protect (no password to be used), I suspect that I do not have it
in the proper arrangement / location. Can someone help?

Option Explicit
Sub RANKING_SORT()
ActiveSheet.Unprotect
Dim source As Range
With ActiveSheet
Set source = .Range(.Range("A11"), .Range("AR11").End(xlDown))
End With
source.Sort source.Range("V11"), xlAscending
ActiveSheet.Protect
End Sub

 

[JLGWhiz]

It worked fine for me the way it is.

 

[usmc-r70]

The 'unprotect' works, but the 'protect' does not. If I manually 'protect'
the sheet, the code will 'unprotect' and execute the code, but will not
'protect'. I recorded a macro of 'protecting' and 'unprotecting' the
worksheet and added the additional code to the:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

But still no 'protect'. Any ideas?

Option Explicit
Sub RANKING_SORT()
ActiveSheet.Unprotect
Dim source As Range
With ActiveSheet
Set source = .Range(.Range("A11"), .Range("AR11").End(xlDown))
End With
source.Sort source.Range("V11"), xlAscending
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[usmc-r70]

Not sure if it makes a difference, I am using Office 2007.

 

[Per Jessen]

Hi

It worked fine for me in both Excel 2000 and Excel 2007.

You could try to step through the code with F8 and check if all statements
are executed.

Regards,
Per

 

[usmc-r70]

I stepped through as you suggested with the following results:
OK Sub RANKING_SORT()
OK ActiveSheet.Unprotect
SKIPPED Dim source As Range
OK With ActiveSheet
OK Set source = .Range(.Range("A11"), .Range("AR11").End(xlDown))
OK End With
OK source.Sort source.Range("V11"), xlAscending
NO ACTION ActiveSheet.Protect
UNKNOWN End Sub

 

[usmc-r70]

I removed a specific module and its code: i.e. the following:
Function HasFormula(c As Range) As Boolean
HasFormula = c.HasFormula
End Function

And the aforementioned code works, do you know why?

 

[Per Jessen]

Hi

The code that you posted can not prevent the sheet from beeing protected.
But maybe this UDF is called from an event code in the sheet module or in
ThisWorkbook module ?

My guess is that you have some event code which unprotect the sheet but
never protect it again.

//Per