+ Reply to Thread
Results 1 to 9 of 9

Run-time error 1004 when sheet is protected

  1. #1
    Registered User
    Join Date
    05-05-2008
    Posts
    4

    Run-time error 1004 when sheet is protected

    Hi All,

    I've been "Googling" this all day and can't work this out. I'm really new to VBA and need very basic instructions on how to fix this. From what I've read it's a common problem, I just don't know what to do.

    I'm basically trying to sort the data and it works fine when unprotected but I get the error message when protected. When I go to debug, the following is highlighted.

    Please Login or Register  to view this content.
    Can anyone help please? Thanks.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    You will need to unprotect your sheet.

    This can be done via VBA as long as you know the password
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    AJ,

    I've moved your post to programming. This link should help you

    http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm

    Post back if you still have any questions

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  4. #4
    Registered User
    Join Date
    05-05-2008
    Posts
    4
    Thanks Guys.

    VBA Noob, I have read that page over and over yesterday and still can't figure it out. Where do in the VBA script do you put that? I've tried at the start, the end, in the middle! I still can't get it to work.

  5. #5
    Registered User
    Join Date
    04-09-2008
    Location
    Michigan, USA
    Posts
    60
    The unprotect goes before the script that is editing the worksheet.

    When the script is done editing the wooksheet. the protect sould be used to reprotect the workshees.

    Something like this..

    Please Login or Register  to view this content.
    Hope this helps.
    Last edited by erich_7719; 05-05-2008 at 07:50 PM.

  6. #6
    Registered User
    Join Date
    05-05-2008
    Posts
    4
    Thanks erich. That makes more sense and I think has almost fixed the issue. However, I'm getting a new error. "Run-time error 9: Script out of range." When I go to debug I get the following highlighted.

    Please Login or Register  to view this content.
    At the moment, the whole script looks like this.

    Please Login or Register  to view this content.
    Does this look right?

  7. #7
    Registered User
    Join Date
    04-09-2008
    Location
    Michigan, USA
    Posts
    60
    Please Login or Register  to view this content.
    First things first, I myself am prety much just starting out with VBA. So with that said...

    Two things could be happening That I can think of...

    1. you are using the wrong name (should be in quotes) or variable.

    or

    2. You have mistyped the password.

    This is one of my set-ups (password has been changed for my protection).
    simple as this for unprotection.

    Please Login or Register  to view this content.
    And to protect

    Please Login or Register  to view this content.
    Password - states the password
    DrawingObjects - protects any objects
    Contents - protects locked cells
    Scenarios - not to sure what these are but I left it in
    UserInterfaceOnly - will not alow VBA to select a cell but VBA alown can edit the cells.

    something you could do is

    Please Login or Register  to view this content.
    Last edited by erich_7719; 05-05-2008 at 09:28 PM.

  8. #8
    Registered User
    Join Date
    05-05-2008
    Posts
    4

    Thumbs up

    Thanks for that erich. It looks like it has worked!

    ok, I think the rules of this forum state that I've got to say how you helped or how I fixed it.

    1. You told/taught me that I have to use the code

    Please Login or Register  to view this content.
    2. You also taught me that I have to split the code. i.e.
    Please Login or Register  to view this content.
    at the start and
    Please Login or Register  to view this content.
    at the end.

    3. I had also left out the " " around the name of my sheet and my password in the script. Once I added the " " marks it fixed it up.

    Thanks very much!!

  9. #9
    Registered User
    Join Date
    04-09-2008
    Location
    Michigan, USA
    Posts
    60
    Glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1