lock used cells

M

MIke

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
 
J

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
 
M

MIke

JE,

Thanks. It works great.

Mike

JE McGimpsey said:
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
Click to expand...
 
M

MIke

JE,

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

Thanks,
Mike
 
J

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.
 
G

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
 
Top