Hi Wayne,

Good idea, certainly stops users straying out of bounds. If they tab from
say G20 H20 sends them back to B1, trust that's not disconcerting.

With your ActiveCell.Next.Activate I wonder if it's even necessary to
protect the sheet at all. But perhaps best also to do that as a double
safeguard.

Worth noting that use of ActiveCell.Next.Activate causes the SelectionChange
event to run a second time, not a problem with the code as-is but might be
if you include code to do other things.

Curiosity, do you use the optional extra I threw in to allow easy access for
an administrator. Also do you use the "Change" event aimed at preventing
paste over entire rows (I never tested that properly).

Regards,
Peter T

"wAyne" wrote in message
...
Hi Peter,

I added one more thing to your code, its working great by the -
for others that may read this - I added the following::
ActiveCell.Next.Activate just after you protect the sheet, if it is to be
protected - that way when the user hits tab he goes to the next
unprotected
cell...

here is the new version...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
ActiveCell.Next.Activate
End If

End Sub


wAyne_

p.s. this actually works better and gives you more control than just
protecting the worksheet on its own .. at least in Excel2000 whe you
aren't
given as many choices in what to protect.

thanks again peter...

"Peter T" wrote:

Hi wAyne

Maybe protect the sheet if user selects locked cells (alternatively
Intersect with col's A or H), but unprotect if user selects anything
else,
including entire rows. First unlock all cells, then lock the two
columns.

Sub SetLock()

With Worksheets(1) ' change to suit
.Activate
.Unprotect
.Cells.Locked = False
.Range("A:A,H:H").Locked = True
.Range("B2").Select
End With

End Sub


' in worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean
Dim v As Variant

If Target.Columns.Count = Me.Columns.Count Then
'entire row(s) selected
b = True

ElseIf (Range("B1") = "password") Then ' optional
' allow administrator to edit the locked cells
b = True

Else
v = Target.Locked = False
If Not IsNull(v) Then
b = v
' Else
' b = False ' mixed locked & non-locked
End If

' or with hard coded range of the locked cells
' b = Intersect(Target, Range("A:A,H:H")) Is Nothing
End If

If b Then
Me.Unprotect
Else
Me.Protect
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

For Each r In Target.Areas
If r.Columns.Count = Me.Columns.Count Then
If Application.CountA(r) Then

' not empty so not deleted, probably pasted with values
MsgBox "Not with entire rows, will now Undo"

Application.Undo ' if this doesn't work try Ctrl-z
' Application.SendKeys ("^z")
Exit For
End If
End If
Next

End Sub


Not sure about the "Change" event, an attempt to stop user pasting with
values over entire row(s). I'm slightly surprised Application.Undo works
(for me) in this context, I had expected to need SendKeys Ctrl-z.
Some loopholes though, eg paste rows of empty values - but that's
similar to
deleting rows.

Regards,
Peter T

"wAyne" wrote in message
...
I hope someone can help .... Excel 2000

I have a spreadsheet where users need to be able to change values in
columns
B:G but not in Column A or H...

I could lock the necessary cells and protect the sheet, however....
The users should be able to delete the enitre row if they wish.

I thought of using the Private Sub Worksheet_Change(ByVal Target As
Range)
function - but it executes after the cell is changed. and is there a
way
to
determine if the cell is being changed or the entire row was deleted?

any ideas?

Thanks
wAyne_