CODEWITHSUNDEEP
excelvba
aurora
aurora

Reputation: 33

VBA - Data Entry Form

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: enter image description here

Upvotes: 0

Views: 239

Answers (1)

ArcherBird
ArcherBird

Reputation: 2134

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.

Upvotes: 1

Related Questions