Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Protect / unprotect worksheet through vba

This is a discussion on Protect / unprotect worksheet through vba within the Excel Questions forums, part of the Question Forums category; I am creating workbookA by taking a templateA and pasting in information from workbookB. The goal is to have the ...

  1. #1
    New Member
    Join Date
    Feb 2004
    Posts
    19

    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, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    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
    Crested Butte, CO
    Posts
    27,280

    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

    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, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    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

    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
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    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

    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

    Default Re: Protect / unprotect worksheet through vba

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

  10. #10
    New Member
    Join Date
    Dec 2010
    Posts
    48

    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

Page 1 of 2 12 LastLast

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