Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with sorting data on a protected sheet..in (Excel97)

    I'm using Excel97
    I try to sort a data range in a protected sheet..but
    everytime it gives me an error..the error is :

    Run-time error '1004':

    Sort method of Range class failed

    It will shade the range that I'm trying to sort..



    How do I overcome this problem coz its very annoying..
    Many thanks

    ps : theres no password on that proctected sheet just empty.i mean just hit enter or ok
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problem with sorting data on a protected sheet..in (Excel97)

    I believe you will have to issue an unstruction to remove the sheet protection before sorting, even if the password is blank. I woudl try:
    <pre>ActiveSheet.Unprotect password:=""
    </pre>

    or just
    <pre>ActiveSheet.Unprotect
    </pre>

    I am not sure of the syntax; whenever I protect a sheet I use a pasword, so I have not had to use the 'no password' version

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem with sorting data on a protected sheet..in (Excel97)

    You can include the code to unprotect the worksheet befor you sort it, and reprotect when the sorting is done. Amend your macro to the following :<pre>Sub Sorting()
    <font color=red>ActiveSheet.Unprotect</font color=red>
    Range("A1:A9").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    <font color=red>ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True</font color=red>
    End Sub</pre>

    Just add the lines in red to your existing code and it should work.

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks for both of you but Andrew.....

    i'm thinking like this..lol..

    for Andrew solution..it just a thought okay..
    let say the sheet is protected with password
    let say "123"..so how can i use your code..

    OR

    i still hunt for this..
    can you guys figure a way..i meant how
    to sort data range on a protected sheet
    because i have saw a guy do this..sort data
    range on a protected sheet without unprotect
    the sheet first.many thanks guy..hope you can
    help me on this..appreciate it very much and
    zillion thanks

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks for both of you but Andrew.....

    Addition to my previous reply..

    What I meant by using your code Andrew
    is how can I manipulate your code if
    the protected sheet contain password..
    let say "123" your code that your provide
    will prompt for password if the sheet
    contains pasword.how can i overcome this
    and the macro will unprotect it without
    prompt for password and after finished, it
    will protect again the sheet with "123"
    password..hope you can understand my question
    andrew..thanks again..

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Thanks for both of you but Andrew.....

    Put these two lines at the beginning of the Sub
    Dim strPwd As String
    strPwd = "123"
    ... and use
    'unprotect line:
    ActiveSheet.Unprotect Password:=strPwd
    '<sort code>
    're-protect line:
    ActiveSheet.Protect Password:=strPwd
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks for both of you but Andrew.....

    Thanks..it works like a charm..

    but still looking for the code to sort the data range without unprotect it first ..is it possible or is it just a dream..lol..thanks a bunch guys..

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks for both of you but Andrew.....

    Hi,

    If you use a macro to protect the sheet, you can use a setting called UserInterfaceOnly and set it to True. This will allow you to have the sort done by a macro, even though the sheet is protected.

    Hope this is what you are looking for.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks for both of you but Andrew.....

    By the way, this also applies to using the filter on a protected sheet, but here you have to add the enableautofilter method of the activesheet object and set it to true:

    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect PassWord:="MyPassWord", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceonly:=True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •