Software

Ask something
Discussion Locked

Sort cells in protected Excel'97 Macro

0 helpful
3 replies

by heavyduty

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I want to sort a range of cells using a macro in Excel 97. The basic macro works fine. But the ultimate goal of the worksheet is an enduser form that is "dummy" proof, so most cells are locked, and many hidden. The range in question is NOT locked, but when I protect the sheet and run the macro that sorts the range, I get a run-time error (1004) and the message "Sort method of Range class failed."

Any ideas on how to write a VBA module that will sort this range even in a protected sheet?
Sample VBA ...

Range("AW67:AW88").Select
Selection.Sort Key1:=Range("AW67"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("AX67:AX88").Select
Selection.Sort Key1:=Range("AX67"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

3 replies

by dklippert

Add
ActiveWorkbook.Unprotect Password := "password"
...bunch'o code
ActiveWorkbook.Protect Password := "password"
End Sub

by shanghai sam

I had to modify your suggestion, but it got me thinking in the right way. There were syntax and other minor glitches to work through, but it works now. Code ended up ...

ActiveSheet.Unprotect Password:="password"
...bunchocode...
ActiveSheet.Protect Password:="password"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

This discussion is closed to new replies.