Check sibling questions

Example 1:

Make a user form which enters data Expense Name and Amount 

And after clicking submit, row should be filled like 


Download file and try

 

Step 1: Make form

  1. Go to visual basic editor (Alt + F11)
  2. Insert -> User form
  3. Expense Name and Amount are labels. boxes are textbox and submit button is Command Button.
  4. Change captions
  5. Double click on submit button.
  6. Download and copy this from Private Sub to End Sub

    Private Sub submit_Click()
    Dim LastRow As Long, ws As Worksheet

    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

    ws.Range("D" & LastRow).Value = TextBox1.Text 'Adds the TextBox1 into Col D & Last Blank Row
    ws.Range("E" & LastRow).Value = TextBox2.Text 'Adds the TextBox2 into Col E & Last Blank Row
    End Sub

  7. Edit UserForm name to DataForm and caption to Enter Data.

 

Step 2: Add button to show form 

  1. Add a button (Insert -> Shapes).
  2. Right click -> Assign macro -> New 
  3. Between Sub and End Sub, add the line
    DataForm.Show

 What is the meaning of the code?

View answer

 

Eg 1(b) : Add a party name in the form 

View answer

To add party name,

  1. We need to add Party name in our form.
  2. Adding column of party name
  3. Inserting the following line in Submit button Code before End Sub
    ws.Range(" F " & LastRow).Value = TextBox3. Text    'Adds the TextBox3 into Col F & Last Blank Row

 

Eg 1(c): Add a date column in the table which shows today's  date.

 

View answer

  1. Add a date column in the table
  2. Inserting the following line in Submit button Code before End Sub
    ws.Range("G" & LastRow).Value = Now()     ' Adds today's date 
  1. Excel

About the Author

CA Maninder Singh

CA Maninder Singh is a Chartered Accountant for the past 14 years. He also provides Accounts Tax GST Training in Delhi, Kerala and online.