CODEWITHSUNDEEP
vbaexcel
John M.
John M.

Reputation: 347

How to tweak this data entry form

I have a userform with one text field and one submit button on it. I want the user to be able to enter a name in the text field and click the submit button and have the name entered into the spreadsheet. After clicking submit I would like the last name to disappear and leave the cursor in the text field so the person can continue with entering another name.

The closes that I've been able to get it is the person enters a name and clicks the Submit button and it is entered into the spreadsheet but I must then reselect the old name in order for it to disappear when the person begins typing the next name.

Is there a way to automatically blank out the name in the text field and move the cursor to it so the person is poised and ready to enter the next name.

Here is the code behind my Submit button. Thank you.

Private Sub CommandButton1_Click()
    Dim i As Integer
    i = 1
        While ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value <> ""
        i = i + 1
        Wend
    ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value = TextBox1.Value
End Sub

Upvotes: 2

Views: 117

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

Try this one:

Private Sub CommandButton1_Click()
    Dim i As Integer
    i = 1
    While ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value <> ""
        i = i + 1
    Wend
    TextBox1.Value = ""
    TextBox1.SetFocus
End Sub

Btw, if you need to write value in cell, next after last non empty cell, use this one:

Private Sub CommandButton1_Click()
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A" & .Rows.Count).End(xlUp).Offset(1).Value = TextBox1.Value
    End With
    TextBox1.Value = ""
    TextBox1.SetFocus
End Sub

Upvotes: 2

Related Questions