 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
INPUT BOX
Another very handy function is the InputBox function. This will allow us to collect a reply from a user in the form of text. Its syntax is:
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
The only required argument is the Prompt and this would be the message you display to the user.
Try this example below:
Sub WhatsYourName()
Dim stReply As String
'<any code>
stReply = InputBox(Prompt:="What is your name?", _
Title:="OzGrid Example", Default:="Mine is David!")
If stReply <> "" _
And stReply <> "Mine is David!" Then
MsgBox "Hello " & stReply
End If
End Sub
In the above example we are asking the user to tell us their name. We have put a default message in the InputBox telling them our name. As with the message box function, we are using a variable to determine their reply. The difference is though, the InputBox returns a String and not an Integer! If the user decides to Cancel the InputBox it would return empty text (""), if they simply clicked Ok without changing our Default message it would return: "Mine is David!". Both of these possibilities are dealt with in our If And combination.
There is also another type of InputBox, which is a member of the Application Object. This InputBox will allow us to specify what type of value to pass back to our InputBox. The syntax for this InputBox is:
expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)
Again the only required argument is the Prompt. The important part to note with this type of InputBox is that you must precede it with an Application Object, which in Excel's case in the actual word Application
The return value type is specified by the Type argument and is shown in the table below taken from the Excel help.:
| Value | Meaning |
|---|---|
| 0 | A formula |
| 1 | A number |
| 2 | Text (a string) |
| 4 | A logical value (True or False) |
| 8 | A cell reference, as a Range object |
| 16 | An error value, such as #N/A |
| 64 | An array of values |
You can use the sum of the allowable values for Type. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.
So to use the InputBox to return a range Object you could use:
Sub Demo()
Dim rMyRange As Range
On Error Resume Next
Set rMyRange = Application.InputBox _
(Prompt:="Select any range", Title:="OzGrid Example", Type:=8)
rMyRange.Select
On Error GoTo 0
End Sub
Notice the use of On Error Resume Next this is used to prevent any possible run time errors that would occur if the user clicks Ok (without select a range) or Cancel. If they did, our Set Statement would not be valid. I have then used the On Error GoTo 0 to reset all run time errors. The use of these two Statements are discussed in a later lesson.
As you can see, with the use of Excels built in function such as MsgBox and InputBox, we are able to communicate with the user. More importantly, we can collect their replies and answers.