Submit button to write on another table-CodePudding

I'm trying to create a form by VBA. Therefore, I'm using buttons to collect information and then, when clicking in the submit button, It should write the answers on another sheet, in a specific table. The next time you will use it and submit, the excel should add an extra line.

I have wrote a code but instead of starting to write the answers on the 1st line of the table (Cell B7), it starts at the end of it (B544). Do you have any idea why?

Links to the problem:

The end of the table

The beginning of the table

VBA Code

CodePudding user response：

Since you are counting the table rows to use that number for the position parameter in the Add() method of ListRows object, I suppose you want to add a new row at the bottom of the table

To add a new row at the bottom of a ListObject object, just omit the position parameter

Private Sub CommandButton1_Click()
    Dim row As ListRow

    Set row = ActiveWorkbook.Worksheets("Teste-Base de dados").ListObjects("Table1").ListRows.Add
        row.Range(1) = ...
        row.Range(2) = ...

        ...

End Sub

which you can also "reduce" to:

Private Sub CommandButton1_Click()
       
    With ActiveWorkbook.Worksheets("Teste-Base de dados").ListObjects("Table1").ListRows.Add
        .Range(1) = ...
        .Range(2) = ...
    End With

    ...

End Sub

While if you want to add the new row as the first one, then use 1 for the position parameter

Private Sub CommandButton1_Click()
    Dim row As ListRow

    Set row = ActiveWorkbook.Worksheets("Teste-Base de dados").ListObjects("Table1").ListRows.Add(1)
        row.Range(1) = ...
        row.Range(2) = ...

        ...

End Sub

or its "collapsed" twin

Private Sub CommandButton1_Click()

    With ActiveWorkbook.Worksheets("Teste-Base de dados").ListObjects("Table1").ListRows.Add(1)
        .Range(1) = ...
        .Range(2) = ...
    End With
    
    ...
    
End Sub

CodePudding user response：

Thanks for the help, this approach worked for me:

Private Sub CommandButton1_Click()
    Dim row As ListRow

    Set row = ActiveWorkbook.Worksheets("Teste-Base de dados").ListObjects("Table1").ListRows.Add(1)
        row.Range(1) = ...
        row.Range(2) = ...

        ...

End Sub

However, I would like to know how can I add new rows one after another (if the first row has values, it should add values to the 2nd row, and so on).

For example, this first approach will add new rows at the end of the table, no matter if it has values or not (the table could be empty):

 Dim row As ListRow

    Set row = ActiveWorkbook.Worksheets("Teste-Base de dados").ListObjects("Table1").ListRows.Add

On the second approach, one row will be added on the 1st position, while the remaining rows will go down (to the 2nd, 3rd, 4th,.... row):

Dim row As ListRow

    Set row = ActiveWorkbook.Worksheets("Teste-Base de dados").ListObjects("Table1").ListRows.Add(1)