Protect - Unprotect code location
 

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

Protect - Unprotect code location
 

It worked fine for me the way it is.


"usmc-r70" wrote in message
...
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

Protect - Unprotect code location
 

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



"JLGWhiz" wrote:


It worked fine for me the way it is.


"usmc-r70" wrote in message
...
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

Protect - Unprotect code location
 

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

"JLGWhiz" wrote:


It worked fine for me the way it is.


"usmc-r70" wrote in message
...
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

Protect - Unprotect code location
 

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" skrev i meddelelsen
...
Not sure if it makes a difference, I am using Office 2007.

"JLGWhiz" wrote:


It worked fine for me the way it is.


"usmc-r70" wrote in message
...
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

Protect - Unprotect code location
 

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

"Per Jessen" wrote:

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" skrev i meddelelsen
...
Not sure if it makes a difference, I am using Office 2007.

"JLGWhiz" wrote:


It worked fine for me the way it is.


"usmc-r70" wrote in message
...
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

Protect - Unprotect code location
 

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" wrote:

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" skrev i meddelelsen
...
Not sure if it makes a difference, I am using Office 2007.

"JLGWhiz" wrote:


It worked fine for me the way it is.


"usmc-r70" wrote in message
...
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

Protect - Unprotect code location
 

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

"usmc-r70" skrev i meddelelsen
...
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" wrote:

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" skrev i meddelelsen
...
Not sure if it makes a difference, I am using Office 2007.

"JLGWhiz" wrote:


It worked fine for me the way it is.


"usmc-r70" wrote in message
...
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