UserForm basics in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this VBA code in a UserForm?

Consider a UserForm with a TextBox named TextBox1 and a CommandButton named CommandButton1. The following code runs when the button is clicked:

Private Sub CommandButton1_Click()
    MsgBox "Hello, " & TextBox1.Text
End Sub

What happens when the user types Anna in the TextBox and clicks the button?

Excel

Private Sub CommandButton1_Click()
    MsgBox "Hello, " & TextBox1.Text
End Sub

A A message box appears showing: Hello, Anna

B The TextBox clears and no message appears

C An error message appears because TextBox1.Text is invalid

D Nothing happens when the button is clicked

💡 Hint

Think about how the TextBox's Text property is used in the message box.

The code concatenates the string "Hello, " with the text entered in TextBox1. So if the user types "Anna", the message box will show "Hello, Anna".

❓ Function Choice

intermediate

Which VBA function correctly closes a UserForm?

You want to close a UserForm named MyForm when a button is clicked. Which VBA statement will close the form properly?

A Exit UserForm

B Close MyForm

C Unload Me

D End MyForm

💡 Hint

Remember that Me refers to the current UserForm inside its code.

The Unload Me statement closes the current UserForm properly. Other options are not valid VBA syntax for closing forms.

🎯 Scenario

advanced

How to transfer UserForm input to a worksheet cell?

You have a UserForm with a TextBox named TextBoxName and a CommandButton. When the button is clicked, you want to put the text from the TextBox into cell A1 of Sheet1. Which code snippet inside the button click event will do this correctly?

A Range("A1").Text = TextBoxName.Text

B Cells(1, 1).Value = TextBoxName.Text

C Sheet1.Cells(1, 1) = TextBoxName.Value

D Worksheets("Sheet1").Range("A1").Value = TextBoxName.Text

💡 Hint

Use the full reference to the worksheet and the Text property of the TextBox.

Option D correctly references Sheet1 and assigns the TextBox's Text property to cell A1's Value. Option D tries to assign to Range("A1").Text which is read-only; Option D uses Sheet1.Cells(1, 1) = TextBoxName.Value, which may work if Sheet1 is the sheet's code name; Option D lacks worksheet reference and may cause errors.

❓ data_analysis

advanced

What error occurs with this UserForm code?

Inside a UserForm, you have this code:

Private Sub CommandButton1_Click()
    Dim num As Integer
    num = CInt(TextBoxNumber.Text)
    MsgBox 100 / num
End Sub

If the user leaves TextBoxNumber empty and clicks the button, what happens?

Excel

Private Sub CommandButton1_Click()
    Dim num As Integer
    num = CInt(TextBoxNumber.Text)
    MsgBox 100 / num
End Sub

A Message box shows 100

B Runtime error: Invalid use of Null or empty string in CInt

C Message box shows 0

D No error, message box shows Infinity

💡 Hint

What happens if you convert an empty string to an integer?

Trying to convert an empty string with CInt causes a runtime error because it cannot convert empty text to a number.

🧠 Conceptual

expert

How does the Me keyword behave inside a UserForm?

In VBA UserForm code, what does the keyword Me refer to?

A The current UserForm instance where the code runs

B The Excel workbook containing the UserForm

C The active worksheet in Excel

D The module where the code is written

💡 Hint

Think about object-oriented programming and how Me is used in class modules.

Inside a UserForm's code, Me refers to the UserForm itself, allowing you to access its controls and properties easily.

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