Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Protect / unprotect worksheet through vba

  1. #1
    New Member
    Join Date
    Feb 2004
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Protect / unprotect worksheet through vba

    I am creating workbookA by taking a templateA and pasting in information from workbookB. The goal is to have the finished workbookA with some cells locked, and some unlocked. My code works fine until I tried to protect the worksheet in the template. Now it can't paste info into it, since it is locked.

    I figure my two choices are:
    (1) start with template unprotected, paste info in, then protect it
    (2) start with template protected, unprotect it in vba, paste in info, protect it

    Any suggestions as to the best approach? I'm not protecting it for security, just to restrict where people key in information.

    Any examples would be really appreciated also.

    Thanks
    Steve

  2. #2
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Start the macro recorder, do your stuff, then look at the code it generates
    Kristy

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Protect / unprotect worksheet through vba

    Here you go:


    Sub ProtectAll()
    Dim sh As Worksheet
    Dim myPassword As String
    myPassword = "password"

    For Each sh In ActiveWorkbook.Worksheets
    sh.Protect Password:=myPassword
    Next sh

    End Sub

    Sub UnrotectAll()
    Dim sh As Worksheet
    Dim myPassword As String
    myPassword = "password"

    For Each sh In ActiveWorkbook.Worksheets
    sh.Unprotect Password:=myPassword
    Next sh

    End Sub


    Hope that helps,

    Smitty

  4. #4
    New Member
    Join Date
    Feb 2004
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect / unprotect worksheet through vba

    Kristy and Smitty, thanks for the quick replies...

    I've made progress, but have one 'minor' problem.

    I've added the code from Smitty, which works fine for unprotecting the template so I can paste, and then protects it. The problem is that when it protects it, the parameters are such that you can select locked and unlocked cells.

    I've used the macro recorder, as Kristy suggested, and it shows this code:

    Code:
        sh.EnableSelection = xlUnlockedCells
        sh.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True
    which, according to the help, should result in users only being able to select unlocked cells. But when I go into the resulting worksheet, it is 'protected', but the user can select locked cells. THey can't change them, as a message pops up, but I don't want them to be able to go into those cells.

    Any thing else I need to try?
    Thanks
    Steve

  5. #5
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try putting the EnableSelection line of code from the recorder in Smitty's "ProtectAll" macro before the password line.

    It worked for me...
    Kristy

  6. #6
    New Member
    Join Date
    Feb 2004
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect / unprotect worksheet through vba

    Kristy,
    here is the code as I currently have it:
    Code:
    For Each sh In ActiveWorkbook.Worksheets
        sh.EnableSelection = xlUnlockedCells
        sh.Protect
    Next sh
    I have no password currently assigned to the protection, so the 'sh.protect' line has no password parameter. I attempted to put the EnableSelection parameter on the protect line, but Excel didn't like that.

    So I'm a little lost with your last instruction; I don't want to put the command outside the 'for each' loop, and it's already in front of the line that had the password and protect method.

    Sorry I'm so dense...

    Steve

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,933
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect / unprotect worksheet through vba

    Steve,

    What you've done is correct. The problem is that the EnableSelection property will only remain in place as long as the workbook is open. As soon as you close it and re-open it you'll be able to select any cells again. I don't know why Microsoft did this but I'm sure they have some inane reason. Anyway, one possible solution is to put your code in the Workbook_Open event procedure. See the Workbook Modules section from my site if you're unsure how to do this (sorry that the page looks a bit crappy but I'm in the middle of renovating!).

    HTH
    Dan

  8. #8
    New Member
    Join Date
    Feb 2004
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect / unprotect worksheet through vba

    Dan, you (along with Kristy and Smitty) saved my bacon. I put the code into the workbook open procedure and it works fine (after I swapped the order - had to put the protect first, then the enable selection...
    Thanks everyone. This site is super.
    Steve

  9. #9
    New Member
    Join Date
    Oct 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect / unprotect worksheet through vba

    worksheets("SheetName").protect userinterfaceonly: = True

  10. #10
    Board Regular
    Join Date
    Dec 2010
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect / unprotect worksheet through vba

    Hello:

    I needed some help, I am trying to create a macro to protect sheets within the workbook file. below code is what I used but it is not working. I would keep this file open then go into each seperate workbook file to protect all the sheets in the file. Also I added a ctrl a to protect and ctl key r to unprotect the file. key Any help would be appreciated:

    Sub AA___Protect_All_FY14_Calc_Sheets()
    'AProtect All Calc Sheets Macro
    'FY13 password is "test", macro must be updated to include "FY13" in array to function
    Dim ws As Worksheet
    Set WSArray = Sheets(Array("Census", "Emp_Data_7_1_13"))
    Set WSArray = Sheets(Array("Calc Sheet (1)", "Calc Sheet (2)", "Calc Sheet (3)", "Calc Sheet (4)", "Calc Sheet (5)", "Calc Sheet (6)", "Calc Sheet (7)", "Calc Sheet (8)", "Calc Sheet (9)", "Calc Sheet (10)", "Calc Sheet (11)", "Calc Sheet (12)", "Calc Sheet (13)", "Calc Sheet (14)", "Calc Sheet (15)", "Calc Sheet (16)", "Calc Sheet (17)", "Calc Sheet (18)", "Calc Sheet (19)", "Calc Sheet (20)", "Calc Sheet (21)", "Calc Sheet (22)", "Calc Sheet (23)", "Calc Sheet (24)", "Calc Sheet (25)", "Calc Sheet (26)", "Calc Sheet (27)", "Calc Sheet (28)", "Calc Sheet (29)", "Calc Sheet (30)", "Calc Sheet (31)", "Calc Sheet (32)", "Calc Sheet (33)", "Calc Sheet (34)", "Calc Sheet (35)", "Calc Sheet (36)", "Calc Sheet (37)", "Calc Sheet (38)", "Calc Sheet (39)", "Calc Sheet (40)", "Calc Sheet (41)"))
    Set WSArray = Sheets(Array("Calc Sheet (42)", "Calc Sheet (43)", "Calc Sheet (44)", "Calc Sheet (45)", "Calc Sheet (46)", "Calc Sheet (47)", "Calc Sheet (48)", "Calc Sheet (49)", "Calc Sheet (50)", "Calc Sheet (51)", "Calc Sheet (52)", "Calc Sheet (53)", "Calc Sheet (54)", "Calc Sheet (55)", "Calc Sheet (56)", "Calc Sheet (57)", "Calc Sheet (58)", "Calc Sheet (59)", "Calc Sheet (60)", "Calc Sheet (61)", "Calc Sheet (62)", "Calc Sheet (63)", "Calc Sheet (64)"))
    For Each ws In WSArray
    ws.Protect Password:="test1", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next ws
    End Sub
    Sub AA___UnProtect_All_FY14_Calc_Sheet()
    'UnProtect All Budget Sheets Macro
    Dim ws As Worksheet
    'Set WSArray = Sheets(Array("census", "Emp_Data_7_1_13"))
    Set WSArray = Sheets(Array("Census", "Emp_Data_7_1_13"))
    Set WSArray = Sheets(Array("Calc Sheet (1)", "Calc Sheet (2)", "Calc Sheet (3)", "Calc Sheet (4)", "Calc Sheet (5)", "Calc Sheet (6)", "Calc Sheet (7)", "Calc Sheet (8)", "Calc Sheet (9)", "Calc Sheet (10)", "Calc Sheet (11)", "Calc Sheet (12)", "Calc Sheet (13)", "Calc Sheet (14)", "Calc Sheet (15)", "Calc Sheet (16)", "Calc Sheet (17)", "Calc Sheet (18)", "Calc Sheet (19)", "Calc Sheet (20)", "Calc Sheet (21)", "Calc Sheet (22)", "Calc Sheet (23)", "Calc Sheet (24)", "Calc Sheet (25)", "Calc Sheet (26)", "Calc Sheet (27)", "Calc Sheet (28)", "Calc Sheet (29)", "Calc Sheet (30)", "Calc Sheet (31)", "Calc Sheet (32)", "Calc Sheet (33)", "Calc Sheet (34)", "Calc Sheet (35)", "Calc Sheet (36)", "Calc Sheet (37)", "Calc Sheet (38)", "Calc Sheet (39)", "Calc Sheet (40)", "Calc Sheet (41)"))
    Set WSArray = Sheets(Array("Calc Sheet (42)", "Calc Sheet (43)", "Calc Sheet (44)", "Calc Sheet (45)", "Calc Sheet (46)", "Calc Sheet (47)", "Calc Sheet (48)", "Calc Sheet (49)", "Calc Sheet (50)", "Calc Sheet (51)", "Calc Sheet (52)", "Calc Sheet (53)", "Calc Sheet (54)", "Calc Sheet (55)", "Calc Sheet (56)", "Calc Sheet (57)", "Calc Sheet (58)", "Calc Sheet (59)", "Calc Sheet (60)", "Calc Sheet (61)", "Calc Sheet (62)", "Calc Sheet (63)", "Calc Sheet (64)"))
    For Each ws In WSArray
    ws.Unprotect Password:="test1"
    Next ws
    End Sub

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
  •  


DMCA.com