Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 5 - Messages and User Inputs

Lesson 5.3 - The VBA Input Box

Topic 5.3.2 - Setting a Default Value

If there's likely to be a common response to the question you're asking, you can save your users time by providing a default value for the input box:

A default value is displayed in the input box as soon as it appears. You can accept the default value, or simply type in a new one.

Files Needed

You don't need any files for this part of the lesson.

Completed Code

You can click here to download a file containing the finished code.

Create a new workbook and insert a new module.

Specifying a Default Value

You can use the optional Default parameter to provide an initial value for an input box. Create a subroutine called DefaultCake and add code to it as shown below:

This code assigns the string Chocolate to the default value of the input box.

Running the code shown above produces an input box with a value already entered:

You could type over the default value but there seems little point in this particular case.

Calculating a Default Value

You don't have to pass a literal value to the default parameter; you can also use the result of an expression. Create a new subroutine called DefaultDate and add code to it as shown below:

This code uses the Date and Format functions to pre-fill the input box with a formatted version of the current date.

Running this procedure displays an input box with today's date entered as the default:

The default value you see will depend on the date when you run the procedure.

To practise setting a default value for an input box:

Using any workbook, create a new subroutine called DefaultDogs.
Add code to ask the user if they prefer dogs or cats and capture the result in a variable:

Sub DefaultDogs()

Dim PetChoice As String

PetChoice = InputBox( _

Prompt:="Dogs or cats?", _

Title:="Best Pets")

End Sub

Alter the code so that the input box shows the best type of pet (that's Dogs in case you weren't sure) as its default value:

Sub DefaultDogs()

Dim PetChoice As String

PetChoice = InputBox( _

Prompt:="Dogs or cats?", _

Title:="Best Pets", _

Default:="Dogs")

End Sub

Add an If statement to check that the user has entered the correct answer and display a message in either case:

Sub DefaultDogs()

Dim PetChoice As String

PetChoice = InputBox( _

Prompt:="Dogs or cats?", _

Title:="Best Pets", _

Default:="Dogs")

If LCase(PetChoice) = "dogs" Then

MsgBox "Correct! Doggos are the best."

ElseIf LCase(PetChoice) = "cats" Then

MsgBox "Wrong! Cats are the worst."

Else

MsgBox "Wrong, " & PetChoice & _

" are rubbish, dogs are the best." & _

vbNewLine & "Or maybe sloths."

End If

End Sub

Run the subroutine and check that you see the correct response:

Remember: dogs are best, but anything is better than a cat. Even spiders.

Save and close the file.
Now go and buy a dog (optional).

This page has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Specifying a Default Value

Calculating a Default Value

Head office

London

Manchester