Results 1 to 3 of 3

Thread: Protect worksheet problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Protect worksheet problem

    Hi everyone,

    So the default for protect sheet is "select unlocked cells" only. I want to enable "format cells" which is all good when I do it manually, HOWEVER, when I run the code...

    activesheet.unprotect
    'other code
    activesheet.protect

    it appears to reset the protected elements back to the default. Therefore, my worksheet becomes overprotected and whenever I want to format a cell, i need to manually go back and change the settings again. I also have a stopwatch timing assigned macro that I really want to keep the code to an absolute minimum so that it runs FAST because I want to record multiple time stamps to within around a 10th of a second. I could simply add activesheet.unprotect to the time stamp macro, but every line slows it down.

    Is there a method that will not cause the protect sheet elements to reset to the default when the above VBA code is run??

  2. #2
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,292
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect worksheet problem

    Have you tried recording a macro whilst setting the protection you require?

    You should come up with something like this:

    Code:
    Sub Macro1()
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True
    End Sub
    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect worksheet problem

    Thanks Dom, that should do it.

    Cheers
    Didj

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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