Microsoft Office Tutorials and References
In Depth Information
Chapter 12: Custom Dialog Box Alternatives
Notice that this InputBox function is written in a Do Until loop to ensure that something is
entered when the input box appears. If the user clicks Cancel or doesn’t enter any text,
UserName contains an empty string, and the input box reappears. The procedure then attempts
to extract the first name by searching for the first space character (by using the InStr function)
and then using the Left function to extract all characters before the first space. If a space
character isn’t found, the entire name is used as entered.
As I mentioned, the InputBox function always returns a string. If the string returned by the
InputBox function looks like a number, you can convert it to a value by using VBA’s Val
function. Or you can use Excel’s InputBox method, which I describe in the next section.
Figure 12-2 shows another example of the VBA InputBox function. The user is asked to fill in
the missing word. This example also illustrates the use of named arguments. The prompt text is
retrieved from a worksheet cell and is assigned to a variable ( p ).
Figure 12-2: Using VBA’s InputBox function with a long prompt.
Dim TheWord As String
Dim p As String
Dim t As String
p = Range(“A1”)
t = “What’s the missing word?”
TheWord = InputBox(prompt:=p, Title:=t)
If UCase(TheWord) = “BATTLEFIELD” Then
MsgBox “That is incorrect.”