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

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Specifying a Default Value

Calculating a Default Value

Head office

London

Manchester