Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need VBA script to auto-insert value upon row insert
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need VBA script to auto-insert value upon row insert
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need VBA script to auto-insert value upon row insert
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need VBA script to auto-insert value upon row insert
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 "Phil" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need VBA script to auto-insert value upon row insert
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 "Phil" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto insert row: | Excel Worksheet Functions | |||
auto insert | Excel Discussion (Misc queries) | |||
Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
auto row insert | Excel Worksheet Functions | |||
Auto Insert of Rows | Excel Worksheet Functions |