Add row with new ID-CodePudding

I made a code where I can add a new row to a table:

Private Sub CommandButton1_Click()
    SaveOptions.Hide
    Dim myRow As ListRow
    Dim intRows As Integer
    intRows = ActiveWorkbook.Worksheets("formations").ListObjects("formations").ListRows.Count
    Set myRow = ActiveWorkbook.Worksheets("formations").ListObjects("formations").ListRows.Add(intRows)
    
End Sub

I am now trying to add an automatic ID to that row based on the last ID available. So, if the old highest ID was 123, it would add a row with an ID of 124. If it was 124, it would add one with 125, etc.

Both the worksheet I'm working and the table I want to add the new row are called "formations".

Thanks!

CodePudding user response：

I have found a way to do what you have asked. I had to adjust your original code so that the row added was at the end of the table, and I had to decide where the ID column would be. I have included some instruction on how the column can be changed. Additionally, I made the sub in a way that it will check if IDs are present in the first row of the table. This is done so that if no IDs are in the sheet it will operate as intended.

Private Sub CommandButton1_Click()

'@Variable myRow is the row which will be added to the table.
Dim myRow As ListRow

'@Variable modBook is the workbook containing the table to be modified. 
'Change this by using a specific workbook if the active book is not the one 
'containing the table.
Dim modBook As Workbook
    Set modBook = ActiveWorkbook
    
'@Variable modSht is the sheet containing the table to have a row added to. 
'Change this by changing the sheet name.
    Dim modSht As Worksheet
        Set modSht = modBook.Worksheets("Formations")
    
'@Variable modTable is the table to be modified. Change this by changing the name of the table.
Dim modTable As ListObject
    Set modTable = modSht.ListObjects("formations")
    
'@Variable intRows is the number of rows in the table with one extra so that the row will be placed
'at the end of the table, and so that the number will be placed into the newest row.
Dim intRows As Integer
    intRows = modTable.ListRows.Count   1
    
'@Variable IDNum is the ID Number to be put into the new column.
Dim IDNum As Integer

'@Variable IDCol is the column that contains the ID Numbers. Change this by changing "A" to another column.
Dim IDCol As Range
    Set IDCol = modSht.Range("A2:A" & intRows)
    
'Checks if the first cell with an ID Number is not 1. If it is not it adds ID numbers to every row in the table
'then adds the new row and gives it the ID Number next in line.
If IDCol.Cells(2, 1).Value < 1 Then
    IDNum = 1
    For Each cell In IDCol
            cell.Value = IDNum
            IDNum = IDNum   1
    Next
    
    Set myRow = modTable.ListRows.Add(intRows)
    IDCol.Cells(intRows, 1).Value = IDNum
'If the first cell is 1 or greater then the ID Column contains IDs so the last row in the table will become the new
'ID Number.
Else
    IDNum = Cells(intRows, 1).Value   1
    Set myRow = modTable.ListRows.Add(intRows)
    modSht.Cells(intRows   1, 1).Value = IDNum
End If

End Sub

If you have any questions about how it works, please let me know. Hope this helps! Edited to include changes so that IDs are printed to the "Formations" sheet.

CodePudding user response：

Excel Table (`ListObject`): Add a New Row With a New Id

Short

Sub AddTableRow()

    ' Reference the table ('tbl').
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Formations").ListObjects("Formations")
    
    ' Reference the id list column ('ilc').
    Dim ilc As ListColumn: Set ilc = tbl.ListColumns("Id")
    
    ' Add and reference a new list row.
    With tbl.ListRows.Add
        ' Write the new id to the new id cell.
        .Range.Cells(ilc.Index).Value = Application.Max(ilc.DataBodyRange)   1
    End With
    
End Sub

Step By Step

Sub AddTableRowStudy()

    ' Define constants.
    Const wsName As String = "Formations"
    Const tblName As String = "Formations"
    Const ilcName As String = "Id"
    
    ' Reference the objects.
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim tbl As ListObject: Set tbl = ws.ListObjects(tblName)
    Dim ilc As ListColumn: Set ilc = tbl.ListColumns(ilcName)
    
    ' Write the maximum number in the id column's data body range (no headers),
    ' increased by 1 (next id), to a long variable ('NewId').
    Dim NewId As Long: NewId = Application.Max(ilc.DataBodyRange)   1
    
    ' Add and reference a new list row ('nlr').
    Dim nlr As ListRow: Set nlr = tbl.ListRows.Add
    
    ' Using the 'Index property of the ListColumn object',
    ' reference the new cell ('nCell'), the cell
    ' in the id column's new (last) list row.
    Dim nCell As Range: Set nCell = nlr.Range.Cells(ilc.Index)
    
    ' Write the new id to the new cell.
    nCell.Value = NewId
    
End Sub

Excel Table (ListObject): Add a New Row With a New Id

Excel Table (`ListObject`): Add a New Row With a New Id