Excel vba simple textbox insert sub error-CodePudding

I'm making a very easy application to insert names and some other info , and I'm getting a problem in the sub. I don't know what's happening , been a long time since I used vba ....

    Private Sub button_Click()

Dim linha As Long

   linha = Worksheets("FAMINHO_ESCOLAS").cell(Rows.Count, 1).End(xlUp).Row   1
   
   Range("A" & linha).Value = boxname.Value
   Range("B" & linha).Value = boxinstr.Value
   Range("C" & linha).Value = boxescola.Value
   Range("D" & linha).Value = boxtel.Value
   Range("E" & linha).Value = boxemail.Value

End Sub

I'm getting error 438

CodePudding user response：

If you change the values of fields within the form, the values in the array will be changed to reflect the changes.
That is, the value of the name (text) is stored in the array, so the value that is stored in the array will be different each time it is changed.

CodePudding user response：

linha is set to the last row but LR is the variable that is actually used for the last row.

linha = Worksheets("FAMINHO_ESCOLAS").Cell(Rows.Count, 1).End(xlUp).Row   1

Cell( should be changes to Cells(.

linha = Worksheets("FAMINHO_ESCOLAS").Cells(Rows.Count, 1).End(xlUp).Row   1

It would be better to qualify Rows.Count to the worksheet.

I prefer to write a separate sub routine to add the values. In this way, I can test the code without having to instantiate a userform.

Alternative Solution

Note: AddRowToFAMINHO_ESCOLAS will accept anywhere from 1 to 69 values.

Private Sub button_Click()
    AddRowToFAMINHO_ESCOLAS boxname.Value, boxname.Value, boxinstr.Value, boxescola.Value, boxtel.Value, boxemail.Value
End Sub


Sub AddRowToFAMINHO_ESCOLAS(ParamArray Args() As Variant)
    With Worksheets("FAMINHO_ESCOLAS")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(1, UBound(Args)   1).Value = Args
    End With
End Sub