Prompting for a cell value

The following procedure demonstrates how to ask the user for a value and then insert it into cell A1 of the active worksheet:

Sub GetValue1 ()

Range("A1").Value = InputBox("Enter the value") End Sub

Figure 11-3 shows how the input box looks.

Figure 11-3: The InputBox function gets a value from the user to be inserted into a cell.

This procedure has a problem, however. If the user clicks the Cancel button in the input box, the procedure deletes any data already in the cell. The following modification takes no action if the Cancel button is clicked:

Sub GetValue2()

Dim UserEntry As Variant

UserEntry = InputBox("Enter the value") If UserEntry <> "" Then Range("A1").Value = UserEntry End Sub

In many cases, you'll need to validate the user's entry in the input box. For example, you may require a number between 1 and 12. The following example demonstrates one way to validate the user's entry. In this example, an invalid entry is ignored, and the input box is displayed again. This cycle keeps repeating until the user enters a valid number or clicks Cancel.

Sub GetValue3 ()

Dim UserEntry As Variant Dim Msg As String Const MinVal As Integer = 1 Const MaxVal As Integer = 12

Msg = "Enter a value between " & MinVal & " and " & MaxVal Do

UserEntry = InputBox(Msg) If UserEntry = "" Then Exit Sub If IsNumeric(UserEntry) Then

If UserEntry >= MinVal And UserEntry <= MaxVal Then Exit Do End If

Msg = "Your previous entry was INVALID." Msg = Msg & vbNewLine

Msg = Msg & "Enter a value between " & MinVal & " and " & MaxVal

Loop

ActiveSheet.Range("A1").Value = UserEntry

End Sub

As you can see in Figure 11-4 , the code also changes the message displayed if the user makes an invalid entry.

Figure 11-4: Validate a user's entry with the VBA InputBox function. CD-ROM

The three Getvalue procedures are available on the companion CD-ROM. The filename is inputbox demo.xlsm .

0 0

Post a comment