Excel 2019 Bible

Chapter 47: Seeing Some VBA Examples

47

FIGURE 47.2

Using the VBA InputBox function to get a value from the user

You can assign this value to a variable and use it in your procedure. Often, however, you
want to place the value into a cell. The following procedure demonstrates how to ask the
user for a value and place it into cell A1 of the active worksheet, using only one statement:

Sub GetValue()

Range("A1").Value = InputBox("Enter the value for cell A1")

End Sub

This procedure has a problem, however: if the user clicks Cancel, the contents of cell A1 are
replaced with an empty string. Here’s a modified version in which the InputBox entry is
assigned to a variable named UserVal. The code checks this variable and takes action only
if the variable is not empty:

Sub GetValue()

UserVal = InputBox("Enter the value for cell A1")

If UserVal <> "" Then Range("A1").Value = UserVal

End Sub

Here’s a variation that accepts only a numeric value. If the user enters a non-number, the
InputBox keeps appearing until a number is entered. Only when a number is entered does
the code exit the Do Loop, and the value is entered into A1. Another line inside the loop
allows the user to click Cancel and get out of the procedure:

Sub GetValue()

Do

UserVal = InputBox("Enter a numeric value for cell A1")

If UserVal = "" Then Exit Sub

Loop Until IsNumeric(UserVal)

Range("A1").Value = UserVal

End Sub