lock used cells
 

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

lock used cells
 

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

In article ,
MIke wrote:

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

lock used cells
 

JE,

Thanks. It works great.

Mike

"JE McGimpsey" wrote:

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

In article ,
MIke wrote:

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

lock used cells
 

JE,

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

Thanks,
Mike

"JE McGimpsey" wrote:

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

In article ,
MIke wrote:

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

lock used cells
 

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.


In article ,
MIke wrote:

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

lock used cells
 

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


On Fri, 19 Oct 2007 09:30:01 -0700, MIke wrote:

JE,

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

Thanks,
Mike

"JE McGimpsey" wrote:

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

In article ,
MIke wrote:

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

lock used cells
 

Missed one part.

Move the code to Thisworkbook module from sheet module.


Gord

On Fri, 19 Oct 2007 15:13:20 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


On Fri, 19 Oct 2007 09:30:01 -0700, MIke wrote:

JE,

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

Thanks,
Mike

"JE McGimpsey" wrote:

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

In article ,
MIke wrote:

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