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.

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 checks for the Cancel button clicks but takes no action:

Sub GetValue2()

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

Microsoft Excel

□ 1

Enter the value

OK 1

Cancel |


1 1

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

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 MinVal As Integer, MaxVal As Integer

Dim UserEntry As String

Dim Msg As String

Dim DblEntry As Double

MinVal = 1

MaxVal = 12

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

UserEntry = InputBox(Msg)

If UserEntry = "" Then Exit Sub

If IsNumeric(UserEntry) Then DblEntry = Val(UserEntry)

If DblEntry >= MinVal And DblEntry <= MaxVal Then Exit Do

End If

Msg = "Your previous entry was INVALID."

Msg = Msg & vbNewLine

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


ActiveSheet.Range("A1").Value = UserEntryEnd Sub

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

Microsoft Excel

Your previous entry was INVALID.

1 0K 1

Enter a value between 1 and 12

Cancel |

1 _I

1 1

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

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

The InputBox function returns a string,so I use VBA's Val function to convert the string to a value prior to using the If statement to perform the numerical comparisons.

Was this article helpful?

0 0

Post a comment