MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Locking a cell

Posted by Rock on April 13, 2001 7:52 AM

Does anyone know how to lock a cell to prevent someone from tampering with a formula in a particular cell? I don't want to lock the worksheet nor workbook.

Posted by Dave Hawley on April 13, 2001 4:55 PM

Hi Rock

You would need some VBA for this. Right click on your sheet name tab and select "View Code" and paste in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Written by OzGrid Business Applications
Dim WatchCell As Range

Set WatchCell = Range("A1")
On Error GoTo ResetEvents

If Not Intersect(Target, WatchCell) Is Nothing Then
Application.EnableEvents = False
MsgBox "You cannot select cell A1", vbCritical
End If

Set WatchCell = Nothing
Application.EnableEvents = True

End Sub

Now change "Range("A1")" to the cell you want protected and "Range("A2")" to a cell they are taken to if they do select it. Push Alt+Q to return to Excel

OzGrid Business Applications