UserForm basics in Excel - Dashboard Guide

Dashboard Mode - UserForm basics

Dashboard Goal

Help users enter sales data easily using a UserForm in Excel. The dashboard shows how to input data through the form and see it listed in the sheet.

Sample Data

Sale ID	Product	Quantity	Price	Total
1	Pen	10	1.5	15
2	Notebook	5	3	15
3	Eraser	7	0.5	3.5
4	Marker	3	2	6
5	Ruler	4	1	4

Dashboard Components

• UserForm Input Fields: Text boxes for Product, Quantity, and Price. A button to add the data to the sheet.
• Data Table: Shows all sales entries with columns Sale ID, Product, Quantity, Price, and Total.
• Formulas: In the Total column, formula =C2*D2 calculates total price per sale.
• VBA Code Snippet: Adds new entry from UserForm to next empty row in the sheet and clears form fields.

Private Sub btnAdd_Click()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Sheets("SalesData")
  Dim nextRow As Long
  nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
  ws.Cells(nextRow, 1).Value = nextRow - 1 ' Sale ID
  ws.Cells(nextRow, 2).Value = Me.txtProduct.Value
  ws.Cells(nextRow, 3).Value = Val(Me.txtQuantity.Value)
  ws.Cells(nextRow, 4).Value = Val(Me.txtPrice.Value)
  Me.txtProduct.Value = ""
  Me.txtQuantity.Value = ""
  Me.txtPrice.Value = ""
  Me.txtProduct.SetFocus
End Sub

Dashboard Layout

+----------------------+-------------------------+
|      UserForm         |      Sales Data Table    |
|  [Product:    ]       | +----+---------+-----+-----+|
|  [Quantity:   ]       | | ID | Product | Qty | $   | |
|  [Price:      ]       | +----+---------+-----+-----+|
|  [Add Button ]        | | 1  | Pen     | 10  | 1.5 |
+----------------------+ | 2  | Notebook| 5   | 3   |
                             | 3  | Eraser  | 7   | 0.5 |
                             | 4  | Marker  | 3   | 2   |
                             | 5  | Ruler   | 4   | 1   |
                             +----+---------+-----+-----+

Interactivity

When the user types product name, quantity, and price in the UserForm and clicks the Add button, the VBA code adds a new row to the Sales Data table. The Total column automatically calculates the total price. The form fields clear and focus resets for next entry.

Self Check

Try adding a new sale with Product = "Pencil", Quantity = 8, Price = 1.2. What will be the new Sale ID and Total value in the table?

Key Result

Excel dashboard with a UserForm to input sales data and a table showing all sales with calculated totals.