Locking Data Cells

[Peter W Soady \(UK\)]

Hi

I have a bit of a quandary. I would like to know if it is possible to lock
cells once the Enter / Return Key has been pressed or the cursor has been
moved from the cell. I don't care if it is Macro or VBA as long as the cell
can be locked.

A simple example would be were you enter data in say cell C5, press Enter. I
would like to lock that cell from being edited with the exception of
Administrators.

Many thanks in advance for any help I get

Peter (UK)

 

[okrob]

You can have it lock the cell with an event proceedure, although that
won't do you much good without protecting the sheet. I think what
you're asking is; Is there a way to prevent someone from editing a
cell once they enter data in to that cell?
And the short answer to that is: No.
Rob

 

[Gord Dibben]

Peter

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
'for a single cell............
If Target.Address = "$C$5" Then
'For a range
'If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
'for a column..............
'If Target.Cells.Column = 3 Then
ActiveSheet.Unprotect Password:="justme"
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.

NOTE: first select all cells and Format to "Unlocked"


Gord Dibben MS Excel MVP

 

[okrob]

Peter

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
'for a single cell............
If Target.Address = "$C$5" Then
'For a range
'If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
'for a column..............
'If Target.Cells.Column = 3 Then
ActiveSheet.Unprotect Password:="justme"
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.

NOTE: first select all cells and Format to "Unlocked"

Gord Dibben MS Excel MVP







- Show quoted text -

Mr. Dibben,
While that works, it still won't prevent anyone from just unprotecting
the sheet. They don't have to be an admin, they just have to know how
to view the code. The password is right there...
Rob

 

[Gord Dibben]

You now go into the VB Editor and place protection on the workbook code.

Then users cannot see the password or any code.

Alt + F11 then CTRL + r

Right-click on your workbook/project and Properties.

Set "Lock for viewing" and give it a password..........not the same as the sheet
password.

Save and close to set the protection.

When you re-open right-clicking on the sheet tab will pop up a password to view
dialog.


Gord

 

[Peter W Soady \(UK\)]

Thanks guys

Will let you know how I get on.

Appreciate the time. Again Many thanks