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.

Practice

1. What is the main purpose of a UserForm in Excel VBA?

easy

A. To format cells automatically

B. To write complex formulas in cells

C. To protect worksheets from editing

D. To create a custom dialog box for easier data entry

Solution

1. Step 1: Understand what UserForms do

   UserForms are designed to create custom dialog boxes that help users enter data easily.

2. Step 2: Compare with other Excel features

   Writing formulas, formatting cells, and protecting sheets are not the main functions of UserForms.

3. Final Answer:

   To create a custom dialog box for easier data entry -> Option D

4. Quick Check:

   UserForms = Custom dialog boxes [OK]

Hint: UserForms = custom windows for data input [OK]

Common Mistakes:

• Confusing UserForms with worksheet protection
• Thinking UserForms write formulas
• Assuming UserForms format cells automatically

2. Which VBA code correctly shows a UserForm named MyForm?

easy

A. Show MyForm()

B. MyForm.Show

C. UserForm.Show(MyForm)

D. DisplayForm MyForm

Solution

1. Step 1: Recall the syntax to show a UserForm

   The correct VBA syntax to display a UserForm is UserFormName.Show.

2. Step 2: Check each option

   Only MyForm.Show matches the correct syntax. Others are invalid VBA commands.

3. Final Answer:

   MyForm.Show -> Option B

4. Quick Check:

   Show UserForm = UserFormName.Show [OK]

Hint: Use .Show after the form name to display it [OK]

Common Mistakes:

• Using incorrect keywords like Show or DisplayForm
• Adding parentheses incorrectly
• Passing the form as a parameter

3. Given this VBA code inside a UserForm button click:

Private Sub CommandButton1_Click()
  TextBox1.Text = "Hello"
  Label1.Caption = TextBox1.Text
End Sub

What will Label1 show after clicking the button?

medium

A. TextBox1.Text

B. CommandButton1_Click

C. Hello

D. Empty

Solution

1. Step 1: Understand the code actions

   The code sets TextBox1.Text to "Hello" and then sets Label1.Caption to the same text.

2. Step 2: Determine Label1's displayed text

   Since Label1.Caption is assigned the value of TextBox1.Text, it will show "Hello".

3. Final Answer:

   Hello -> Option C

4. Quick Check:

   Label1.Caption = TextBox1.Text = "Hello" [OK]

Hint: Label shows TextBox text after assignment [OK]

Common Mistakes:

• Thinking Label1 shows the variable name
• Confusing Caption with Text property
• Assuming Label1 stays empty

4. This VBA code is meant to clear a TextBox named InputBox1 when a button is clicked:

Private Sub ClearButton_Click()
  InputBox1.Text = Null
End Sub

Why might this code cause an error?

medium

A. Because Null is not valid for Text property

B. Because the button name is incorrect

C. Because TextBoxes cannot be cleared

D. Because the event name is wrong

Solution

1. Step 1: Understand the Text property type

   The Text property expects a string, but Null is a special VBA value not valid here.

2. Step 2: Identify correct way to clear TextBox

   To clear, assign an empty string "" instead of Null.

3. Final Answer:

   Because Null is not valid for Text property -> Option A

4. Quick Check:

   Clear TextBox = assign "" not Null [OK]

Hint: Clear TextBox with "", not Null [OK]

Common Mistakes:

• Using Null instead of "" to clear text
• Assuming TextBoxes cannot be cleared
• Confusing event or control names

5. You want a UserForm to add numbers entered in two TextBoxes (TextBox1 and TextBox2) and show the result in a Label (LabelResult) when a button is clicked. Which VBA code correctly does this?

hard

A. LabelResult.Caption = CInt(TextBox1.Text) + CInt(TextBox2.Text)

B. LabelResult.Caption = Val(TextBox1) + TextBox2.Text

C. LabelResult.Caption = TextBox1.Text & TextBox2.Text

D. LabelResult.Caption = TextBox1.Text + TextBox2.Text

Solution

1. Step 1: Understand data types in TextBoxes

   TextBoxes store text, so adding directly concatenates strings instead of numbers.

2. Step 2: Convert text to numbers before adding

   Using CInt() converts text to integers, allowing numeric addition.

3. Step 3: Check each option

   LabelResult.Caption = TextBox1.Text + TextBox2.Text concatenates text; LabelResult.Caption = TextBox1.Text & TextBox2.Text concatenates text with &; LabelResult.Caption = Val(TextBox1) + TextBox2.Text mixes a converted number with raw text, causing a type mismatch error.

4. Final Answer:

   LabelResult.Caption = CInt(TextBox1.Text) + CInt(TextBox2.Text) -> Option A

5. Quick Check:

   Convert text to numbers before adding [OK]

Hint: Convert TextBox text to numbers before adding [OK]

Common Mistakes:

• Adding text directly causing string concatenation
• Incomplete conversion of TextBox values to numbers
• Using & operator which concatenates strings