February 14, 2014

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


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


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


End Sub

Further reading:

XL97: How to Use a UserForm for Entering Data

7 thoughts on “Capture Data from UserForm to Excel Worksheet

  1. 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

  2. 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.

  3. why do all your coding generate syntax errors in office 365. like all code you provide simply seems to be so far out of date. the calls etc just shit a brick when trying to use your code.

  4. Dear All.
    I have watched the video and link of Dinesh Takyar.
    We have tried but it is working.
    Can you tell about Application.InputBox without using VBA.InputBox?

Comments are closed.