Autonumber ID field in table when new row added (VBA) PLEASE HELP!!!

[jwahome2002]

Hello. I have been struggling for the past week to find a way in VBA to autonumber an ID field in an Excel table when a new row is inserted. I want to avoid using a cell formula (which I can do) as there risks with that solution. I have a table with fields "TaskID", "Task","Resource". The ID field is simply a numerical field and the ID only needs to be set to the next value (e.g. last ID = 2, the next value would be 3).

So every time a new record is created (usually by tabbing from the last field of the last row), the ID is generated through the VBA sub.

I have tried several solutions. Some got me close, but required the "Task" field to be updated before the ID would be set. Another attempt created the IDs, but did so when any cell in the row was touched - including the headers. I thought this would be an easier task, but I cannot seem to find anything that works. Any help is greatly appreciated!! Thanks!!!

 

[AmandaCatRuth]

How are you adding the rows? Can you do something like this, or is this what you're trying to avoid?

'select cell in whichever column and new row
activecell.value = activecell.offset(0,-1).value + 1

 

[jwahome2002]

Hi - I'm not sure what you are trying to show? Currently, I simply hit the 'Tab' key from the last field of the last row. It would be nice to do through a macro button or user form, but I am not sure how to do either. Thanks!

 

[Snakehips]

jwahome2002,

I may be able to help but would want clarification on below first.

Will your next ID number be determinable from the contents of the table ID column?

Eg by iD count or max ID +1 or ???
Will table rows ever be deleted.

Otherwise can you tolerate a cell somewhere that keeps track of last ID issued?

Table is extended primarily by last cell + tab.
Also by enter data on line below is a possibility ???

 

[jwahome2002]

Snake - firstly, thanks for responding. I can't tell you how much time I've spend looking for a workable solution. Here are the answers to your questions:

1. Next ID will be Max id +1
2. It is possible (although not probable) that table rows could be deleted.
3. If it makes the solution easier, I am absolutely willing to use another cell to store last ID used
4. Table is primarily extended by last cell + tab
5. Possibly also extended by entering data on line below last row
6. Outside possibility (highly unlikely) that a table row could be inserted in the middle of the table. I can write the process to use either #5 or 6.

Thanks so much for taking a look at this!!!

Regards,
J

jwahome2002,

I may be able to help but would want clarification on below first.

Will your next ID number be determinable from the contents of the table ID column?

Eg by iD count or max ID +1 or ???
Will table rows ever be deleted.

Otherwise can you tolerate a cell somewhere that keeps track of last ID issued?

Table is extended primarily by last cell + tab.
Also by enter data on line below is a possibility ???

 

[Snakehips]

Have reported the issue with this thread.

Tried to PM you but your Inbox is full apparently.

Hope this posts ok here.

See if this helps.
Edit table name and the cell that holds the latest ID number to suit.

Hopefully, it auto-numbers when new line created using tab from last cell and if you select a cell in the line below the current last row of table. It is ok to delete rows from table.
Inserting rows does not appear to trigger the selection change event so ion you do insert you need to then click on a cell in the newly inserted row.

Maybe not perfect but will give you something to work with.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'Assuming Table named 'Table1" edit as necessary


Set IDRef = Range("A1")   '  edit to cell where latest ID number is stored


c = Range("Table1").Column
lr = Range("Table1").Row + Range("Table1").Rows.Count - 1
lc = Range("Table1").Columns.Count


'If cell in table is selected eg click tab and therefore new row created
If Not Intersect(Target, Range("Table1")) Is Nothing Then
If Cells(Target.Row, c) = "" Then           ' Not lr = Cells(1, 2) And Cells(lr, c) = "" Then
    Application.EnableEvents = False
    Cells(Target.Row, c) = IDRef + 1
    IDRef.Value = IDRef.Value + 1
    Application.EnableEvents = True
End If
End If


'If cell in first row below table is selected therefore creates new row
If Not Intersect(Target, Range(Cells(lr + 1, c), Cells(lr + 1, c + lc - 1))) Is Nothing Then
Application.EnableEvents = False
    Cells(lr + 1, c) = IDRef + 1
    IDRef.Value = IDRef.Value + 1
    Application.EnableEvents = True
End If


End Sub

Tony

 

[jwahome2002]

Thanks Tony! I will apply and test this tomorrow. Thanks so much for this! I have struggled in trying to get this to work!

J

 

[jwahome2002]

Tony - thanks for posting this. I tried your code, but it kept throwing errors. I had to Dim IDRef, c, lr, lc to get rid of the error. However, neither scenario - last cell + Tab or typing under last row creates a value in the id field. Table created in A1 and Table Name matches. Am I doing something wrong? Thanks again!

J

 

[Snakehips]

Sounds as if you have Option Explicit so require all variables to be declared which you say is now ok.

My test table was randomly placed with headers in D6:F6 and I plumped for A1 as the cell to hold the latest ID number.
Table can be anywhere you like but the latest ID number cell must be a cell outside of the table. From what you say do you have table starting A1 and code referring to A1 for the latest ID ???

 

[jwahome2002]

Thanks Tony - that makes sense. I'll try that now.

Regards
J