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 with 16+ years of practical experience and 20+ years of teaching experience. At Teachoo, he simplifies Accounts, Tax and GST with step-by-step examples so students can apply concepts confidently in exams and real life.

For an uninterrupted learning experience, students can use Teachoo Black to remove ads and focus better.