lock used cells

[Guest]

I have a spreadsheet that I would like the cells to lock after a value has
been entered. Can I do this with a macro or in the sheet code?

Thanks

Mike

 

[JE McGimpsey]

One way:

Put this in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "drowssap"
Dim rArea As Range
Dim rCell As Range
Me.Unprotect Password:=sPWORD
For Each rArea In Target
For Each rCell In rArea
With rCell
.Locked = Not IsEmpty(.Value)
End With
Next rCell
Next rArea
Me.Protect Password:=sPWORD
End Sub

 

[Guest]

JE,

Thanks. It works great.

Mike

One way:

Put this in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "drowssap"
Dim rArea As Range
Dim rCell As Range
Me.Unprotect Password:=sPWORD
For Each rArea In Target
For Each rCell In rArea
With rCell
.Locked = Not IsEmpty(.Value)
End With
Next rCell
Next rArea
Me.Protect Password:=sPWORD
End Sub

 

[Guest]

JE,

Wll these changes be applied to other worksheets if I need to add them?

Thanks,
Mike

 

[JE McGimpsey]

No. If you want cells to be locked on *every* sheet in the workbook,
change the macro from the Worksheet_Change event to the
Workbook_SheetChange() event in the ThisWorkbook code module.

 

[Gord Dibben]

If you want the code to run on all sheets, current or added, change the event
type to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Missed one part.

Move the code to Thisworkbook module from sheet module.


Gord