How to enforce input and delete order of cells?

[Sam Kuo]

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

 

[Jim Thomlinson]

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).Delete Shift:=xlUp
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub

 

[Sam Kuo]

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

 

[JLGWhiz]

Hi Sam, It sounds to me like you might need to use a UserForm input to
control the entry sequence and what data is entered. You can create code to
edit each entry for content and then put it where it belongs. It is more
effort in code writing, but for critical entries, it might be worth it.

 

[Sam Kuo]

Hi JLGWhiz

Thanks for your suggestion. I guess what I first think might be a simple
task with worksheet is not so simple after all

 

[Sam Kuo]

Hi Jim

I've ended up using userform for this data entry excercise as suggested by
JLGWhiz, and here I'm again...

I now have nothing below the range in the worksheet and am hoping this would
allow me to adopt your code to accomplish my task. My problem now is if I
tried to insert a new row between A1 to A10 with your code, the new row was
added in all columns except column A.

I'm hoping to expand your code to allow deleting entire selected rows (as
your code does now) PLUS adding new entire rows one at a time below selected
row between A1 and A10. Maximum of 10 rows is needed only. Can you please
help?

Your kind assistance in overcoming this issue would really help me keeping
this project moving. Many thanks

Sam