Need VBA script to auto-insert value upon row insert

[Phil]

I have a spreadsheet that is tracking ownerships of land parcels for
importing into a database.

Currently it has about 300+ records (rows). In column A, there is a field
named Sort_Key. They are only whole numbers. The column is formatted as
GENERAL, which when using a whole number, would connote a INTEGER format,
right?

Anyway, the whole idea behind the sort key concept is to allow me a way to
revert back to the sequence that the data was originally entered, in case I
changed the way the spreadsheet was sorted.

I have been told that I will no longer be doing the data input on this
spreadsheet, and because of that, will be forwarding the task of data input
to a much a very inexperienced Excel user. This person will undoubtedly
forget to add the sort key value whenever he adds/inserts a new row, I fear.

To avert this potential catastrophe, the only thing that I can think of is
a VBA script that will automatically insert a new Sort_Key value that is +1
(incremented) from the highest previous value.

------------------------------------------------------------------
The work flow for understanding the construction of the VBA script is as
follows:

1 - User initializes INSERT ROW command.

2a - With Excel listening in the background for any INSERT ROW command, the
VBA script initializes.

2b - A dialog box will pop up informing the user that upon completion of the
VBA script, Excel is going to place a NEW, incremented value in the cell (in
Col A) on the new row, for the new Sort Key.

2c - The user will would be prompted to select EITHER: "YES - proceed with
new Sort Key Value" or "NO - I don't want to generate a new Sort Key at this
time". If the user selects "No ...", then it will return to the cursor
position, and the VBA script will abort.

3a - If the user selects "Yes..", then the VBA script will continue.

3b - The script will read in Col A the MAX value, insert it into the
variable to be incremented, then increment it by 1. The output will be
inserted in the new empty cell in Col A.

4 - Once this is done, the script will place the cursor in the next column
(which is named: Tract No), and the routine will end.

------------------------------------------------------------------
Some other questions/comments:

A) What if the user changes his mind and decides NOT to insert a row, and
wishes to UNDO the new Sort Key? What are the ramifications here, if any?

B) I was also thinking of another way to do this would be to have a custom
button that is assigned to the VBA script. Whenever the user wanted to
insert a new row, and then add a new Sort Key value. Since this could be
construed as a "deliberate" action, it would do away steps 1, 2, and 3a.

C) Is there a hidden value that Excel keeps that could be utilized (in some
way) to satisfy this application?

I hope that I've explained this sufficiently, but if not, please place your
questions and I will respond to them promptly.

Thanks in advance.

Phil

 

[Otto Moehrbach]

A cell that is formatted as General will accept, without change, any whole
number. But that does not connote an Integer format.
Let me suggest another way of doing what you want. You say the Sort_Key
number is in Column A and column B is the Tract number. How about some code
(you call it script) to detect an entry change to any cell in Column B?
When such an entry is detected, the code will check if Column A is occupied.
If it is, the code does nothing. If Column A is empty, the code will detect
the highest number in Column A and enter that number +1 into the Column A
cell? Does that sound like what you want? The code could also be written
to detect if the previous entry in Column B was not a blank, and if that's
the case the code would do nothing. All this would do away with picking up
on a row insert. Otto

 

[Phil]

That would be even better. But keep in mind that the purpose of inserting
the row in the first place is to create a NEW tract number. That is really
the true essence of what I am trying to track here. The sort keys are just
there to ensure that you can revert back to the original state, as I said in
my OP.

 

[Otto Moehrbach]

Then I take it that my suggestion to detect when a tract number entry is
made INTO A BLANK CELL, and to place a Sort_Key number in column A if such
is the case, is what you want? Again, there would not be any query or
detection regarding a new row being inserted. Otto

 

[Otto Moehrbach]

Phil
Try this little macro. This macro will clear the entry in Column A if
the user clears the corresponding entry in Column B. If the user enters
something in any cell in Column B, the macro will determine the largest
number in Column A and will enter one more than that in Column A in the row
of the entry. This is an event macro and must be placed in the sheet module
of your sheet. To access that module, right-click on the sheet tab of your
sheet and select View Code. Pate this macro into that module. "X" out of
the module to return to your sheet. Try this out and let me know if you
need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngColA As Range
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(Target.Value) Then
Target.Offset(, -1).ClearContents
Else
Set RngColA = Range("A2", Range("A" &
Rows.Count).End(xlUp))
Target.Offset(, -1).Value = _
Application.Max(RngColA) + 1
End If
Application.EnableEvents = True
End If
End Sub