Should have mentioned I do not want to use it in the workbook module as I
have another 30 sheets not formatted the same way.
So put this back in

Private Sub Worksheet_Change(ByVal Target As Range)


"Learning VBA" wrote in message
...
I just copied and pasted this.
Still does nothing.

I also figured I did not need to unprotect where it is already
unprotected.

I am using Excel 2007 in 2003 mode if that helps.



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Set isect = Intersect(Target, Range("T13:T22,T46:T55"))
If Not isect Is Nothing Then
If Left(Target.Value, 8) < "Add Name" Then
' ActiveSheet.Unprotect Password:="Pass"
Target.Locked = True
'you don't need the Else if you start with all cells(or at least those in
the ranges) unlocked
' Else
' Target.Locked = False
End If
ActiveSheet.Protect Password:="Pass"
End If

End Sub

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Works for me.

I started with all cells on the sheet unlocked.

As I changed the cell values in the ranges, those cells locked.

For 31 worksheets you would place the code into Thisworkbook module so's
it
runs on every sheet..............assuming the ranges are same on each
sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Set isect = Intersect(Target, Range("T13:T22,T46:T55"))
If Not isect Is Nothing Then
If Left(Target.Value, 8) < "Add Name" Then
ActiveSheet.Unprotect Password:="Pass"
Target.Locked = True
'you don't need the Else if you start with all cells(or at least those in
the ranges) unlocked
' Else
' Target.Locked = False
End If
End If
ActiveSheet.Protect Password:="Pass"
End Sub


Gord Dibben MS Excel MVP

On Sun, 10 Jan 2010 19:48:06 -0200, "Learning VBA"
wrote:

I have this in a worksheet Module.
I have been trying to figure this out for over 2 hours and am having
trouble
getting it to work.

I actually have 2 ranges on each sheet and 31 sheets named 01 - 31
The first range is T13:T22 and the second range is T46:T55
I thought I would work on the first range first before tackling the
second.


Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("T13:T22"))
If Not isect Is Nothing Then
If Left(Target.Value, 8) < "Add Name" Then
ActiveSheet.Unprotect Password:="Pass"
Target.Locked = True
Else
Target.Locked = False
End If
End If
ActiveSheet.Protect Password:="Pass"
End Sub

Each range has the following
Add Name 1 :
Add Name 2 :
And so on

If the Add Name is no longer there I would like that cell to lock,
otherwise
leave it unlocked.


"Learning VBA" wrote in message
...
Sorry I figured it out.
I don't know how to count.

Also added
else target.locked = false


Thanks for your help.


"Learning VBA" wrote in message
...
I sent this yesterday but never showed up in the newsgroup.



put this in the code and No Matter what is in the target it locks the
cell.

I just put in 1 cell "Add Name 1: " and it locked
All other cells in the range stayed unlocked.



If Not isect Is Nothing Then
If Left(Target.Value, 9) < "Add Name" Then


"Per Jessen" wrote in message
...
Hi

Something like this (this is an event code and has to be inserted in
the code sheet for the desired sheet):

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("J10:J25"))
If Not isect Is Nothing Then
If Target.Value < "Add Name" Then
ActiveSheet.Unprotect Password:="JustMe"
Target.Locked = True
ActiveSheet.Protect Password:="JustMe"
End If
End If
End Sub

Regards,
Per


"Learning VBA" skrev i meddelelsen
...
Is there a way to lock a cell if it does not contain data?

Example

I have a range of cells:
J10 though J25
The cells currently have "Add Name" these cells are unlocked for
input.

If they are changed and no longer have "Add Name" I would like to
lock
any cell that has changed.