Excel vba simple textbox insert sub error

user_7395776 0
Viewed 44

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

I'm trying to return the values , when i press the "buttonright" it changes to the next data , and when i press buttonleft it shows me previous data and so on

Private Sub CommandButton1_Click()

GetFAMINHO_ESCOLASLastRow boxname1.Value, boxinstr1.Value, boxescola1.Value, 
boxtel1.Value, boxemail1.Value

End Sub

Function GetFAMINHO_ESCOLASLastRow() As Range
    Dim Target As Range
    With Worksheets("FAMINHO_ESCOLAS")
        Set Target = .Cells(.Rows.Count, 1).End(xlUp)
        Set Target = Intersect(Target.EntireRow, Target.CurrentRegion)
    End With
    
    Set GetFAMINHO_ESCOLASLastRow = Target
End Function
vba excel
1 Answers

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

AddRowToFAMINHO_ESCOLAS Demo

enter image description here

Addendum

This function will return the last row with values in column A.

Function GetFAMINHO_ESCOLASLastRow() As Range
    Dim Target As Range
    With Worksheets("FAMINHO_ESCOLAS")
        Set Target = .Cells(.Rows.Count, 1).End(xlUp)
        Set Target = Intersect(Target.EntireRow, Target.CurrentRegion)
    End With
    
    Set GetFAMINHO_ESCOLASLastRow = Target
End Function

You can test this function by entering the following code into the Immediate Window:

Application.Goto GetFAMINHO_ESCOLASLastRow

Response to Question Update

I changed things up a bit because the OP wants to write and retrieve the values.

Private Sub buttonleft_Click()
    Dim Target As Range
    Set Target = GetFAMINHO_ESCOLASLastRow
    With Target
        boxname.Value = .Cells(1, 1).Value
        boxinstr.Value = .Cells(1, 2).Value
        boxescola.Value = .Cells(1, 3).Value
        boxtel.Value = .Cells(1, 4).Value
        boxemail.Value = .Cells(1, 5).Value
    End With
End Sub

Private Sub buttonright_Click()
    Dim Target As Range
    Set Target = GetFAMINHO_ESCOLASNewRow
    With Target
        .Cells(1, 1).Value = boxname.Value
        .Cells(1, 2).Value = boxinstr.Value
        .Cells(1, 3).Value = boxescola.Value
        .Cells(1, 4).Value = boxtel.Value
        .Cells(1, 5).Value = boxemail.Value
    End With
End Sub


Function GetFAMINHO_ESCOLASLastRow() As Range
    Dim Target As Range
    With Worksheets("FAMINHO_ESCOLAS")
        Set Target = .Cells(.Rows.Count, 1).End(xlUp)
        Set Target = Intersect(Target.EntireRow, Target.CurrentRegion)
    End With
    
    Set GetFAMINHO_ESCOLASLastRow = Target
End Function


Function GetFAMINHO_ESCOLASNewRow() As Range
    Set GetFAMINHO_ESCOLASNewRow = GetFAMINHO_ESCOLASLastRow.Offset(1)
End Function
user_99127140
Related