An Input Box example

Here's an example showing how you can use the InputBox function:

TheName = InputBox("What is your name?", "Greetings")

When you execute this VBA statement, Excel displays the dialog box shown in Figure 15-4. Notice that this example uses only the first two arguments and does not supply a default value. When the user enters a value and clicks OK, the routine assigns the value to the variable TheName.

Figure 15-4:

The InputBox function displays this dialog box.

Greetings

What is your name? I; OK il

|johnWalkenbach

The following example uses the third argument and provides a default value. The default value is the username stored by Excel (the Application object's UserName property).

Sub

GetName()

Dim DefName As String

Dim TheName As String

DefName = Application.UserName

TheName = InputBox("What is your name?", _

"Greetings", DefName)

End

VBA's InputBox function always returns a string, so you might need to convert the result to a value. You can convert a string to a value by using the Val function.

The following example uses the Val function to convert the user's entry to a value:

Sub

GetName2AddSheet()

Dim Prompt As String

Dim Caption As String

Dim DefValue as Integer

Dim NumSheets As Integer

Prompt = "How many sheets do you

want to add?"

Caption = "Tell me..."

DefValue = 1

NumSheets = Val(InputBox(Prompt,

Caption, DefValue))

If NumSheets > 0 Then Sheets.Add

Count:=NumSheets

End

Sub

Figure 15-5 shows the dialog box that this routine produces.

Figure 15-5 shows the dialog box that this routine produces.

Figure 15-5:

Another example of using the InputBox function.

Tell me...

How many sheets do you want to add?

m

Cancel |

The information presented in this section applies to VBA's InputBox function. In addition, you have access to the InputBox method, which is a method of the Application object. One advantage of using the InputBox method is that your code can prompt for a range selection. Here's a quick example that prompts the user to select a range. (The Help system has complete details.)

Sub

GetRange()

Dim Rng As Range

On Error Resume Next

Set Rng = Application.InputBox

(prompt:="Specify a range:",

Type:=8)

If Rng Is Nothing Then Exit Sut

)

MsgBox "You selected range " &

Rng.Address

End

Sub

0 0

Post a comment