PDA

View Full Version : Solved: Unprotect cell



outrider
06-22-2005, 04:58 AM
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.

xld
06-22-2005, 05:09 AM
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
06-22-2005, 05:18 AM
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
06-22-2005, 05:43 AM
Thanks for the the quick reply.
Works great.


Outrider.

mdmackillop
06-25-2005, 02:57 PM
Hi Outrider,
I put this together, based upon your question
Regards
MD