Search code examples
excelvbacellspreadsheet-protection

Using VBA to unprotect cells

So I have a spreadsheet that I want to format based on user inputs, but the cells need to remain locked until it is determined by the formatting that they will be user input cells. I have code that will look the cells for the user interface only but allow VBA to edit them.

> 'protects all sheets upon opening work book
>     Me.Worksheets("Sheet1").Protect "Password", UserInterfaceOnly:=True
>     Me.Worksheets("Sheet2").Protect "Password", UserInterfaceOnly:=True
>     Me.Worksheets("Sheet3").Protect "Password", UserInterfaceOnly:=True

so now I need to allow the following cells to be editable by the user

Sheets("Sheets2").Protection.AllowEditRanges.Add "Test", Range("C2", "C8")
Sheets("Sheets2").Protection.AllowEditRanges.Add "Test", Range("H6")
Sheets("Sheets2").Protection.AllowEditRanges.Add "Test", Range("K6")

I have tried allowing edits before I lock the sheet and after. What I'd really like is some VBA code to unlock the cells after applying formatting rather than defining which cells might be editable in advance. Any help gratefully appreciated :)

Solution

  • Change the Locked property of the range.

    Range("C2", "C8").Locked = false

    This is equivalent to using the Format Cells | Protection | Locked checkbox to remove protection from a cell, and will make the cell immediately available for editing.

    Also, for future use, you may want to think about adding a simple loop to the protection step:

    Dim sht as Worksheet

For each sht in ThisWorkbook.Sheets
    sht.protect password:=Password, Userinterfaceonly:=True
Next sht

    That way people can't get around the restriction by adding new sheets, and the protection will continue to work if sheets are renamed, etc.