Unprotect cell

[outrider]

On a password protected spreadsheet, is it possible, for example, to unprotect only cell B18 for editing if cell A12 had specific data put into it?


Thanks for helping.

[Bob Phillips]

On a password protected spreadsheet, is it possible, for example, to unprotect only cell B18 for editing if cell A12 had specific data put into it?

You could trap the event, unprotect the sheet, unlock the cell, and the protect the sheet.

Private Sub Worksheet_Change(ByVal Target As Range) 
On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Target.Address = "$A$12" Then
        If Target.Value = "whatever" Then
            Me.Unprotect Password:="Bob"
            Me.Range("B18").Locked = False
            Me.Protect Password:="Bob"
        End If
    End If
ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in.

[mdmackillop]

Here's an example. It reprotects the cell if the A12 cell value changes. For clarity, the cell colour is changed to reflect protection status.

[outrider]

Thanks for the the quick reply.
Works great.


Outrider.

[mdmackillop]

Hi Outrider,
I put this together, based upon your question
Regards
MD