Example 1:

Make a user form which enters data Expense Name and Amount 

Add user form to fill table - Macros

And after clicking submit, row should be filled like 

filled form filled row.png


Download file and try

 

Step 1: Make form

  1. Go to visual basic editor (Alt + F11)
  2. Insert -> User form
    Insert Userform.png
  3. Expense Name and Amount are labels. boxes are textbox and submit button is Command Button.
    Form tell what is what - Copy.png
  4. Change captions
    change caption.png
  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 

filled form and row.png

View Answer

 

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

  eg3 add date in table.png

View Answer
  1. Excel
Ask Download

About the Author

CA Maninder Singh's photo - Expert in Practical Accounts, Taxation and Efiling
CA Maninder Singh
CA Maninder Singh is a Chartered Accountant for the past 8 years. He provides courses for Practical Accounts, Taxation and Efiling at teachoo.com .