Capture Data from UserForm to Excel Worksheet

How to capture data from a userform and place it in an Excel worksheet

In a new userform create two labels like ‘Name’ and ‘Password’. Then we place two text-boxes next to the labels so that the user can enter his name and password like we do when we log into our Gmail account. Finally we create a command button with a caption ‘capture data’ and code it in such a way that when the user enters his name and password into the text-boxes and clicks on the command button the data is transferred to the Excel worksheet in appropriate cells. If you notice we see only ‘*’ when we enter our password because this text-box has been set to display on ‘*’ when the user enters data into it by changing the property ‘Passwordchar’ to ‘*’ as shown in the video.

The complete VBA code looks like this:

Private Sub CommandButton1_Click()

‘we first find the next blank row for data entry

erow=Sheet1.Cells(Rows.Count,1).End(xlUp),Offset(1,0).Row

‘The name entered goes into cell A2 using the cells property. Cell A1 has the header Name

Cells(erow,1).Value=TextBox1.Text

‘The password goes to cell B2. Cell B1 has the header Password

Cells(erow,2).Value=TextBox2.Text

End Sub

Further reading:

XL97: How to Use a UserForm for Entering Data

2 thoughts on “Capture Data from UserForm to Excel Worksheet

  1. AK

    Hi

    Please how do I capture data from excel sheet to a userform when the user click a button? The aim is that when the user clicks a button, data should be retrieved from row into textbox. The textbox should be updated with new row data when the button is clicked

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *