Prompting for a cell Value

As shown in Figure 14-2, you can use VBA's InputBox function to get a value from the user. Then you can insert that value into a cell. The following procedure demonstrates how to ask the user for a value and place the value in cell A1 of the active worksheet, using only one statement:

Sub GetValue()

Range("A1").Value =

InputBox( _

"Enter the value

for cell A1")

End Sub

Use the VBA InputBox function to get a value from the user.

Figure 14-2:

Use the VBA InputBox function to get a value from the user.

If you try out this example, you find that clicking the Cancel button in the Input Box erases the current value in cell A1. The following macro demonstrates a better approach: using a variable (x) to store the value entered by the user. If the value is not empty (that is, the user didn't click Cancel), the value of x is placed into cell A1. Otherwise, nothing happens.

Sub GetValue2()

Dim x as Variant x = InputBox("Enter the value for cell A1") If x <> "" Then Range("A1").Value = x End Sub

The variable x is defined as a variant because it could be a number or an empty string (if the user clicks Cancel).

0 0

Post a comment