Userform input into a specific cell and go down from there in VBA

Question

I'm a VBA beginner and I've been having some issue with this for a while. I have a worksheet where I need the UserForm to input the clients name into a specific cell. The cell where the first name should go is "B3". When the user submits the info, the form should clear and the next client should be in "B4", and so on. The problem is I can't use the next row option because the next rows are already filled in and need to remain like that. Is there a way to start on "B3" and add one to the last cell each time the user inputs? Thanks for your help!

Answer 1

Depending on which way you want to do this you can have a loop that starts at B3 checks if it is empty. If it is empty it puts information, if not it Increments by 1 to the next row and checks that rows. Loop until it finds an empty cell. Another way if you know rows will be fill uniformily is to simply use the function ActiveSheet.UsedRange.Rows.Count this will give you the row number of the last row with data, then just add 1

Basically it would be :

Private Sub CommandButton1_Click()

Dim I As Long

Sheets("Sheet1").Activate
ActiveSheet.Range("B3").Activate

Do While IsEmpty(ActiveCell.Offset(I, 0)) = False
    I = I + 1
Loop

ActiveCell.Offset(I, 0).Value = ComboBox1.Value

End Sub

Answer 2

You could use End method of the Ranga object. I mean, if you use set rng=Range("E3").End(xlDown) gives you the next used cell on the row, but you have check if rng.Row is Cells.Rows.Count (1.048.576) if all the cells below are empty

Answer 3

I just tried this on a basic userform with a single textbox, so it would need to be slightly modified to fit your needs.

Private Sub TextBox1_Change()
    Application.EnableEvents = False
    Cells(LastRow("Sheet1", "B") + 1, 2).Value = TextBox1.Value
    TextBox1.Value = ""
    Application.EnableEvents = False    
End Sub

Public Function LastRow(SheetName As Variant, Col As Variant) As Long
    Application.Volatile True

    With ThisWorkbook.Sheets(SheetName)
        If .Cells(.Rows.Count, Col).Value <> "" Then
            LastRow = .Rows.Count
            Exit Function
        End If
        LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
    End With
End Function

Also, don't use UsedRange to find the last row. See Error in finding last used cell in VBA