+ Reply to Thread
Results 1 to 11 of 11

Run-time error '1004' ... But only when the worksheet is protected?

  1. #1
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Run-time error '1004' ... But only when the worksheet is protected?

    When my worksheet is unprotected, my macro works perfectly, however after I protect it (and even though the cells affected by the macro are unlocked), I get the following error:

    Run-time error '1004':
    Unable to set the NumberFormat property of the Range class

    When I click on debug, it shoots me to a portion of my VBA code highlighted in yellow:

    Please Login or Register  to view this content.
    Does anyone have any idea what is causing this? I should note that the macro still technically does what it is supposed to when the sheet is protected, its just that I also get the error pop-up.
    Last edited by jonvanwyk; 08-18-2011 at 12:28 PM.

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    Is your offest going to a cell that is still protected?

    PS, even though you have only one line of code displayed in your post, you should still use code tags around it.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    Here is the whole story: I am using the following code to protect the cells from being modified by unauthorized persons:

    Please Login or Register  to view this content.

    This allows those with authority to modify the form, while allowing other users only to click on macro buttons that identify them based on their windows user login; entering their name into a cell and then time stamping the cell next to their name. This allows people to request days off, or volunteer for overtime on a schedule.

    Technically, I wouldn't even need to protect the worksheet since they cannot click on any cells based on the code above, except that they can still right click on the unprotected macro buttons and alter its settings or move it around on the screen, delete them, etc. My solution to this was to only lock the macro buttons, leaving all of the actual cells unlocked since they already cannot be clicked on; and then protect the worksheet. I noticed that I even get the error I describe when the worksheet is protected, but nothing is formatted to be locked. The line of code in my post above that gets highlighted yellow when I click on "debug" is a part of the following sub:

    Please Login or Register  to view this content.

    Thoughts?

  4. #4
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    Hello? *tap *tap *tap ... screeeeeeaaaaaaaaaach ... is this thing on?

  5. #5
    Registered User
    Join Date
    02-07-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    1

    Lightbulb Re: Run-time error '1004' ... But only when the worksheet is protected?

    Excel, macro, run-time error '1004' Unable to set the NumberFormat property of the Range class
    MS Visual Basic 6.0 Runtime Service Pack has to be installed.
    Look for VB6RuntimeSP6.exe.

    Cheers
    Piotr
    Last edited by Umpal; 02-07-2012 at 05:17 AM.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    You have a couple of options.

    1. you can unprotect the sheet before the code and protect it again afterwards:

    Please Login or Register  to view this content.
    or

    2. you can use InterfaceOnly

    Please Login or Register  to view this content.

    You would, if you have not done so already, need to protect your VBA Project with a password.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  7. #7
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    Did you allow formatting cells when you protected the sheet?
    Good luck.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    @OEG0: I'd have thought the OP wouldn't want users to format the sheet, but that's a guess. The formatting is being applied in the code ... at least, that's the desire.

    Regards, TMS

  9. #9
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    @TMS
    I know that. My point is that if the asker has not allowed formatting cells, the code cannot do it either (unless using UserInterfaceOnly:=True as you mentioned) even if the cell is not locked, which is why the error occurs. The asker did ask what the cause was...

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    @OEG0: ah, fair enough. Unprotecting and Protecting is my preferred method ... never really got my head around InterfaceOnly
    Last edited by TMS; 02-07-2012 at 06:39 AM.

  11. #11
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Run-time error '1004' ... But only when the worksheet is protected?

    Mine too. UIO is unreliable, especially where things like data validation or objects are concerned.

+ 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