Thanks Jim. I see this can be very useful if nothing below the range as you
mentioned.

Unfortunately, I do have other stuff below the range and need to keep all 10
input cells where they are. Is there any other possible way?

Sam


"Jim Thomlinson" wrote:

You could give this a whirl. It deletes any blank cells in the range. So long
as you have nothing below the range it should work fine.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Range("A1:A10").SpecialCells(xlCellTypeBlanks).Del ete Shift:=xlUp
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub
--
HTH...

Jim Thomlinson


"Sam Kuo" wrote:

Hi

I have 10 input cells in one column (say A1:A10). To ensure the user enters
input in a subseqent order (i.e. start from A1 and continues down), my
current "Private Sub Worksheet_Change" procedure unlocks the next cell only
when the previous cell has been populated. But this does not really stop the
user from deleting these cells in any order once they are populated and
unlocked...

What I need is to allow the user to input from top down, and if needed,
delete input from botton up, while cell values can be changed (but not
deleted) in any order once it's entered.

I see John's undo subroutine may do the trick as many suggested here (i.e.
undo delete if the deleted cell input is not the last populated one), but
wonder if there is an easier way for my rather simple task?

Any help would be appreciated (example would be even better). Thanks

Sam