UserForm basics in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a UserForm named 'MyForm'.

Excel

Dim [1] As UserForm

Drag options to blanks, or click blank then click option'

AMyForm

BMainForm

CForm1

DUserForm1

Attempts:

3 left

💡 Hint

Common Mistakes

Using a name that is not the default UserForm name.

Trying to declare a UserForm with a name that doesn't exist.

The default UserForm name is usually 'UserForm1' when you add a new form in VBA. However, if you want to create a UserForm named 'MyForm', you declare it as 'MyForm'.

2fill in blank

medium

Complete the code to show the UserForm named 'UserForm1'.

Excel

[1].Show

Drag options to blanks, or click blank then click option'

AUserForm

BForm1

CUserForm1

DMyForm

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong or misspelled UserForm name.

Forgetting to call the Show method.

To display a UserForm, you call the Show method on its name, which is 'UserForm1' by default.

3fill in blank

hard

Fix the error in the code to add a TextBox named 'txtName' to the UserForm.

Excel

UserForm1.Controls.Add "Forms.TextBox.1", "[1]"

Drag options to blanks, or click blank then click option'

Atxtname1

BtxtName

Ctxt_Name

Dtxtname

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect capitalization in the control name.

Adding a control with a name that doesn't follow naming conventions.

Control names are case-sensitive and should match exactly. 'txtName' is the correct name format.

4fill in blank

hard

Fill both blanks to set the caption of a CommandButton named 'cmdSubmit' to 'Submit'.

Excel

UserForm1.[1].[2] = "Submit"

Drag options to blanks, or click blank then click option'

AcmdSubmit

BcmdSubmitButton

CCaption

DText

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Text' instead of 'Caption' property.

Using an incorrect control name.

The property to set the text on a button is Caption, and the button's name is 'cmdSubmit'.

5fill in blank

hard

Fill all three blanks to create a dictionary of TextBox names and their values from the UserForm.

Excel

Dim values As Object
Set values = CreateObject("Scripting.Dictionary")
For Each ctrl In UserForm1.Controls
  If TypeName(ctrl) = "[1]" Then
    values.Add ctrl.[2], ctrl.[3]
  End If
Next ctrl

Drag options to blanks, or click blank then click option'

ATextBox

BName

CText

DCommandButton

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'CommandButton' instead of 'TextBox' in the type check.

Using 'Caption' instead of 'Text' to get the TextBox value.

This code loops through controls, checks if each is a TextBox, then adds its name and text value to a dictionary.

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