VBA - Data Entry Form

Question

I'm doing a Data Entry Table with a From using VBA. The code works fine, but it adds the entry to the first row after the table. For example, if the table's last row is row 20, then it will add the entry in row 21 outside of the table. Is there a way to assign the data to be added in the last row of the table?

This is the code I'm using to add the data to the table:

Private Sub buttonSave_Click()
If MsgBox("Deseas incluir este trabajo?", vbYesNo, "Guardar repuesto") = vbYes Then

    'write the data to the worksheet from controls
    Call WriteDataToSheet
    
    'empty textboxes
    Call EmptyTextBoxes
End If
End Sub

Private Sub WriteDataToSheet()
Dim newRow As Long
With Sheet1
    
    newRow = .Cells(.Rows.Count, 2).End(xlUp).row + 1
    .Cells(newRow, 1).Value = textboxNewDate.Value
    .Cells(newRow, 2).Value = textboxNameNew.Value
    .Cells(newRow, 3).Value = textboxNewEquipment.Value
    .Cells(newRow, 4).Value = textboxNewTech.Value
    .Cells(newRow, 5).Value = textboxNewCot.Value
    .Cells(newRow, 6).Value = textboxNewPrice.Value
    .Cells(newRow, 7).Value = textboxNewFac.Value
    .Cells(newRow, 8).Value = textboxNewStatusF.Value
    .Cells(newRow, 9).Value = textboxNewServ.Value
    .Cells(newRow, 10).Value = textboxNewEnt.Value
    .Cells(newRow, 11).Value = textboxNewDesc.Value
    .Cells(newRow, 12).Value = textboxNewStatus.Value
    
End With 
End Sub

This is the code I'm using to extract the range from the table. This is in a separate module.

Public Function GetType() As Variant 
    GetType = Sheet1.ListObjects("tbTabla2").DataBodyRange.Value
End Function

Is there a way I can assign the data to be added after the last row inside of "Tabla2".

This is the table for reference:

My terrible work around in the past has been to insert a row before the last row, place data in that row, then sort, so the table formatting is maintained. A more appropriate process would determine if that table is purely formatting, which can be added at the end, if it uses named ranges, etc. There are lots of things that can be done, but it almost all has to do with how you generate your table. — Cyril
– Cyril, Commented Jun 9, 2022 at 19:48

ArcherBird · Accepted Answer · 2022-06-09 19:55:48Z

To keep your data inside the table, I suppose you would want to use the ListObject.ListRows.Add() method to add a new row. You could do something like this:

Private Sub WriteDataToTable()
    Dim newTableRow As Range
 
    newTableRow = Sheet1.ListObjects("Table2").ListRows.Add().Range
    WriteDataToRow newTableRow

End Sub

Private Sub WriteDataToRow(row As Range)
    Dim newRowNumber As Long

    newRowNumber row.row
    With Sheet1
        .Cells(newRow, 1).Value = textboxNewDate.Value
        .Cells(newRow, 2).Value = textboxNameNew.Value
        .Cells(newRow, 3).Value = textboxNewEquipment.Value
        .Cells(newRow, 4).Value = textboxNewTech.Value
        .Cells(newRow, 5).Value = textboxNewCot.Value
        .Cells(newRow, 6).Value = textboxNewPrice.Value
        .Cells(newRow, 7).Value = textboxNewFac.Value
        .Cells(newRow, 8).Value = textboxNewStatusF.Value
        .Cells(newRow, 9).Value = textboxNewServ.Value
        .Cells(newRow, 10).Value = textboxNewEnt.Value
        .Cells(newRow, 11).Value = textboxNewDesc.Value
        .Cells(newRow, 12).Value = textboxNewStatus.Value
    End With
End Sub

Now, since you are using newRow = .Cells(.Rows.Count, 2).End(xlUp).row + 1 to find a last-used-row, that implies to me that you could be looking at a case were there is room in your table and you don't want to add a new row. In that case, you'll want to do some sort of conditional test to check if adding the new row is needed. Depending on how things are managed, I think its easier to just ensure the table always ends with the last used row so that you will ALWAYS be adding a row to the table when you want to add data.

Collectives™ on Stack Overflow

VBA - Data Entry Form

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related