Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    runtime error (office 2000)

    I have a mcaro that works like a charm ecah time I use it. I wanted to protect the sheet so I envoked the protection for the sheet . Now eacht time I run the macro I get the run time error. ( 'Run-time error 10004' Sort method of Range class failed )
    However, If I take the protection off ... the macro runs great?????
    any suggestions,
    tia

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: runtime error (office 2000)

    You can NOT sort a protected worksheet. It is protected.

    You can either unprotect sort and then reprotect or when you protect protect for the user only NOT for the macros with a line like this:

    <pre>ActiveSheet.Protect UserInterfaceOnly:=True</pre>


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: runtime error (office 2000)

    Steve,
    Well dugh!!!! How dumb can I be ... That is why it is protected...I'll just sit in the back of the room now thanks...lol [img]/forums/images/smilies/smile.gif[/img]
    later
    Bill

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: runtime error (office 2000)

    Hey, that's rockin Steve...I never knew you could do that!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: runtime error (office 2000)

    It has to be done via a macro command. You can NOT protect it this way from the menus.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: runtime error (office 2000)

    Yeah, I gathered that. I figure I could have taken about 100 lines or so out of the last 1,500 line book I made.

    One question comes to mind that does not seem to be covered in the help file though. Does "user interface" mean solely through the menu? Do you know if for example, a user changed a control in a form and therefore changed the value of a cell, is that considered user interface or would that action be allowed through code?

    I nominate you for the INTERGALACTIC WOPR EXCEL STARPOST OF THE WEEK!

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: runtime error (office 2000)

    Unfortunately, I think that at least the textbox_change event is considered to be "user interface"! Thanks though!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: runtime error (office 2000)

    If you are going to change cells thru comboboxes or textboxes the LINKED cells should be UNLOCKED

    If you want to keep the users prying eyes and fingers off these unprotected cells, put them all on a hidden (or veryhidden) worksheet. You can add a formula (LOCKED of course and maybe even HIDDEN) linking to the UNLOCKED cells. Thus the unlocked cells will change via the objects, and the VALUE in the locked cells will change, but since the FORMULA does NOT change (thus the cell is NOT changing its CONTENTS), excel will NOT trigger a "protected cell" error.

    Steve

Posting Permissions

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