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
'www.ozgrid.com
Dim WatchCell As Range

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

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

ResetEvents:
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


Dave
OzGrid Business Applications