Sort Button on Protected Worksheet


Posted by Glenn on April 22, 2001 1:06 PM

I have a button on a worksheet that activates the macro below. What it does is it sorts, several groups of, a baseball league's teams standings.

I would like to protect the worksheet but still be able to sort the standings. I tried by unlocking the button and protecting the sheet. However, When I hit the button on my worksheet I get an error message and the following highlighted in yellow from debug:

Selection.Sort Key1:=Range("D4"), Order1:=xlDescending, Key2:=Range("A4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

I don't know how to fix the problem.
How can I protect the sheet and still use my button?


Below is the complete macro:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/22/2001 by Glenn Miller
'

'
Range("A4:D8").Select
Selection.Sort Key1:=Range("D4"), Order1:=xlDescending, Key2:=Range("A4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("A11:D15").Select
Selection.Sort Key1:=Range("D11"), Order1:=xlDescending, Key2:=Range( _
"A11"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Range("A18:D21").Select
Selection.Sort Key1:=Range("D18"), Order1:=xlDescending, Key2:=Range( _
"A18"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
ActiveWindow.SmallScroll Down:=17
Range("A24:D28").Select
Selection.Sort Key1:=Range("D24"), Order1:=xlDescending, Key2:=Range( _
"A24"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Range("A31:D36").Select
Selection.Sort Key1:=Range("D31"), Order1:=xlDescending, Key2:=Range( _
"A31"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Range("A39:D43").Select
Selection.Sort Key1:=Range("D39"), Order1:=xlDescending, Key2:=Range( _
"A39"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
End Sub

Thanks
Glenn



Posted by Dave Hawley on April 22, 2001 1:51 PM

Macro4 Macro Macro recorded 4/22/2001 by Glenn Miller '


Hi Glenn

You can uprotect the sheet via VBA like below. I also modified your code so that no ranges are selected.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/22/2001 by Glenn Miller
'

ActiveSheet.Unprotect Password:="secret"

Range("A4:D8").Sort Key1:=Range("D4"), Order1:=xlDescending, Key2:=Range("A4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

Range("A11:D15").Sort Key1:=Range("D11"), Order1:=xlDescending, Key2:=Range( _
"A11"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A18:D21").Sort Key1:=Range("D18"), Order1:=xlDescending, Key2:=Range( _
"A18"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A24:D28").Sort Key1:=Range("D24"), Order1:=xlDescending, Key2:=Range( _
"A24"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A31:D36").Sort Key1:=Range("D31"), Order1:=xlDescending, Key2:=Range( _
"A31"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

Range("A39:D43").Sort Key1:=Range("D39"), Order1:=xlDescending, Key2:=Range( _
"A39"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom

ActiveSheet.Protect Password:="secret"

End Sub

Dave


OzGrid Business Applications