Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
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
UserEntry = InputBox(Msg)
If UserEntry = “” Then Exit Sub
If IsNumeric(UserEntry) Then
If UserEntry >= MinVal And UserEntry <= MaxVal Then Exit Do
Search JabSto ::

Custom Search