Protected Sheet Allows Macro to Execute

[theSizz]

I have a protected worksheet that has a form control button that performs a sort.

Here's the code:

    Sub TestIt()
'
' TestIt Macro
'

'
   
    Range("O2:V80").Select
   
    ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Add Key:=Range("P2:P80") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Recipes").Sort
        .SetRange Range("O2:V80")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

The worksheet is protected. However when the user clicks on the button on the worksheet, the macro runs and performs the sort. Yes, all the cells in the sort area are locked.

Since the worksheet is protected, I would expect that when the user clicks on the button they should get a run time error. I do not want the user to be able to sort the sheet when the protection is on. Why is the macro executing?

Am I correct in assuming that when the user clicks on the form control button that the they should get a run time error and the sort should not perform?

Also there are no options checked off that allows the user to filter the sheet. The only options that are checked are.....allow all users to select locked cells and..... select unlocked cells.

What does this line of code do:

ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Clear

Could this have something to do with it?

Thanks for looking at this.

[p45cal]

Is there, perchance, anywhere in the code the following?
UserInterfaceOnly

[Neeraj Kumar]

I have a protected worksheet that has a form control button that performs a sort.

Here's the code:

    Sub TestIt()
'
' TestIt Macro
'
 
'
 
    Range("O2:V80").Select
 
    ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Add Key:=Range("P2:P80") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Recipes").Sort
        .SetRange Range("O2:V80")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
End Sub

The worksheet is protected. However when the user clicks on the button on the worksheet, the macro runs and performs the sort. Yes, all the cells in the sort area are locked.

Since the worksheet is protected, I would expect that when the user clicks on the button they should get a run time error. I do not want the user to be able to sort the sheet when the protection is on. Why is the macro executing?

Am I correct in assuming that when the user clicks on the form control button that the they should get a run time error and the sort should not perform?

Also there are no options checked off that allows the user to filter the sheet. The only options that are checked are.....allow all users to select locked cells and..... select unlocked cells.

What does this line of code do:

ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Clear

Could this have something to do with it?

Thanks for looking at this.

[Neeraj Kumar]

Hi Thesizz,

[Neeraj Kumar]

Hi Thesizz,
First time you unprotect the sheet and lastly again you protect

Try This Code :

Sub TestIt()
'
' TestIt Macro
'

'
ActiveWorkbook.Worksheets("Recipes").unprotect password:="password"
Range("O2:V80").Select

ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Recipes").Sort.SortFields.Add Key:=Range("P2:P80") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Recipes").Sort
.SetRange Range("O2:V80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Recipes").protect password:="password"
End Sub

[mdmackillop]

Hi Thesizz,
First time you unprotect the sheet and lastly again you protect

You seem to be misreading the question.

I do not want the user to be able to sort the sheet when the protection is on. Why is the macro executing?

[theSizz]

hi p45cal
No where in the code is the UserInterfaceOnly argument used. I realize if this argument is set to True this will allow the macro to run. However, that's not the case. I've posted the entire code and that argument does not appear.

Am I correct in assuming that when the user clicks on the form control button (and the sheet is protected) that the they should get a run time error and the sort should not perform?

[rbrhodes]

hi

Simply test one (or more) of the protection properties.

[VBA]
Sub TestIt()
'
' TestIt Macro
'

'

If ActiveSheet.Protection.AllowSorting = False Then
MsgBox ("Nope")
Exit Sub
End If

'Range("O2:V80").Select

With ActiveWorkbook.Worksheets("Recipes").Sort
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("P2:P80"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("O2:V80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub


[/VBA]