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

3 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
  2. Arindam Mondal

    Hello Sir,
    I want to create an excel sheet which will ask for login details such as username and password to open.
    Once open I want to show the name of the user logged in in a particular cell in a sheet named bill. And also the sheet named bill should also have a button which when clicked logs out the user logged in and display the login form. The data for login details will be fetched from another workbook which will be having only username and password to compare with the entered details in the userform.

    Reply

Leave a Reply

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