+ Reply to Thread
Results 1 to 2 of 2

How do you protect multiple sheets while only allowing users to select unlocked cells?

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Wintersville, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    7

    How do you protect multiple sheets while only allowing users to select unlocked cells?

    I am aware that Excel does not support this function unless you write and run a macro. I have found the code to protect multiple sheets at the same time with a password, but it allows users to manipulate any cell they desire. I have a lot of complex formulas contained on each worksheet and I don't want users to manipulate locked cells, just unlocked ones. I am not worried about the users finding and using the password to unprotect (I highly doubt any of them know what VBA even is, let alone edit the macro) Following is the code I used is found below. Please help, I was never good at writing VBA code.

    Sub ProtectAllSheets()

    For Each ws In ActiveWorkbook.Worksheets

    ws.Protect Password:="secret123"

    Next ws

    MsgBox "All Worksheets Protected"

    End Sub

    Sub UnProtectAllSheets()

    For Each ws In ActiveWorkbook.Worksheets

    ws.Unprotect Password:="secret123"

    Next ws

    MsgBox "All Worksheets Unlocked"

    End Sub

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How do you protect multiple sheets while only allowing users to select unlocked cells?

    Hello,

    Are you perhaps misunderstanding the concept of locking cells and protecting worksheets.

    With an unprotected sheet all cells are available for editing.
    With a protected sheet, unless cell have been unlocked, no cells are available for editing. This is the default condition for a protected sheet.
    In order to allow cell modification with a protected worksheet you need to unlock any relevant cells before protecting the worksheet.

    Think of cells as a field with an electric fence and a separately switched electric gate. If the electricity is turned off there is no restriction. With the electric on the field is protected unless you have been given a key to the switched gate.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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