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:

Default value

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:

Basic default

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:

Input box with default

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:

Default expression

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:

Deafult date

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:

  1. Using any workbook, create a new subroutine called DefaultDogs.
  2. 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

  1. 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

  1. 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

  1. Run the subroutine and check that you see the correct response:
Message

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

 
  1. Save and close the file.
  2. Now go and buy a dog (optional).
This page has 0 threads Add post