Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2007

Tip: Looking for answers? Try searching our database.

Sort a 'Protected' sheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Barney - 04 Jul 2007 13:32 GMT
Using Excel 2002, when I try to sort a protected sheet with a macro, I get a
dialog box with 'Run time error 1004'.  I allowed sorting when I set up the
macro.  Do I need to 'unprotect' the sheet with the macro and then 'protect'
it again after the macro does its sorting?  Any suggestions?

Thanks,

Barney
Don Guillett - 04 Jul 2007 14:17 GMT
As ALWAYS, post your coding efforts for comments

Signature

Don Guillett Excel MVP
SalesAid Software
dguillett1@austin.rr.com

> Using Excel 2002, when I try to sort a protected sheet with a macro, I get
> a dialog box with 'Run time error 1004'.  I allowed sorting when I set up
[quoted text clipped - 4 lines]
>
> Barney
Barney - 04 Jul 2007 14:44 GMT
My macro:

Sub SortScoresDay4()
'
' SortScoresDay4 Macro
' Macro recorded 7/3/2007
'

'
   Range("C4:N69").Select
   Selection.Sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("L4")
_
       , Order2:=xlAscending, Key3:=Range("M4"), Order3:=xlAscending,
Header:= _
       xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
       DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
       xlSortNormal
   Selection.Sort Key1:=Range("N4"), Order1:=xlAscending, Key2:=Range("J4")
_
       , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
       Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=
_
       xlSortNormal
   Range("C4").Select
End Sub

> Using Excel 2002, when I try to sort a protected sheet with a macro, I get
> a dialog box with 'Run time error 1004'.  I allowed sorting when I set up
[quoted text clipped - 4 lines]
>
> Barney
Don Guillett - 04 Jul 2007 15:07 GMT
>>I allowed sorting when I set up  the macro.  Do I need to 'unprotect' the
>>sheet YES

Where is it that you unprotected?

Signature

Don Guillett Excel MVP
SalesAid Software
dguillett1@austin.rr.com

> My macro:
>
[quoted text clipped - 34 lines]
>>
>> Barney
Dave Peterson - 04 Jul 2007 15:39 GMT
Allowing the users to sort via that Tools|Protection|protect sheet dialog (or
the equivalent in code) only gives permission to the users to sort the range.

If you want to sort the range in a macro, you can either unprotect the sheet, do
the sort, and reprotect the sheet--or you could protect the sheet in code and
add UserInterfaceOnly:=true to your code.

Option Explicit
Sub auto_open()
   With Worksheets("sheet1")
       'add all the other settings that you want to the following line
       .Protect Password:="hi", userinterfaceonly:=True
   End With
End Sub

It needs to be reset each time you open the workbook.  That's why I used the
Auto_Open routine.

> Using Excel 2002, when I try to sort a protected sheet with a macro, I get a
> dialog box with 'Run time error 1004'.  I allowed sorting when I set up the
[quoted text clipped - 4 lines]
>
> Barney

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.