Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enforce input and delete order of cells?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enforce input and delete order of cells?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enforce input and delete order of cells?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enforce input and delete order of cells?
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enforce input and delete order of cells?
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 :P "JLGWhiz" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enforce input and delete order of cells?
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enforce format | New Users to Excel | |||
copy cells down a column (in order) to across a row (same order?? | Excel Discussion (Misc queries) | |||
Require mandatory info input in order for Exel to save changes. | Excel Worksheet Functions | |||
Deny change of value of two target cells / enforce use of macros. | Excel Programming | |||
input order | Excel Discussion (Misc queries) |