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
- Go to visual basic editor (Alt + F11)
-
Insert -> User form
-
Expense Name and Amount are labels. boxes are textbox and submit button is Command Button.
-
Change captions
- Double click on submit button.
-
Download and copy this from Private Sub to End Sub
Private Sub submit_Click()
Dim LastRow As Long, ws As WorksheetSet 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 - Edit UserForm name to DataForm and caption to Enter Data.
Step 2: Add button to show form
- Add a button (Insert -> Shapes).
- Right click -> Assign macro -> New
-
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,
- We need to add Party name in our form.
- Adding column of party name
-
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
- Add a date column in the table
-
Inserting the following line in Submit button Code before End Sub
ws.Range("G" & LastRow).Value = Now() ' Adds today's date