vba Input Box

One of the first things new vba programmers try is an inputbox. Maybe this is to get the name of the user. InputBoxes seem simple at first but there is more to using them properly than meets the eye!

Sub Getuser()
Dim strUser As String
strUser = InputBox("Enter your Name")
MsgBox "Hello " & strUser
End Sub

Simple code and will work reliably. Now lets see how someone might use the InputBox method to get a whole number input. They might try code like this.

Sub GetNo()
Dim mywholeNo As Integer
mywholeNo = InputBox("Enter a whole number")
MsgBox "Double this number is " & 2 * mywholeNo
End Sub

Now, this seems to work fin but here's the problem Inputboxes ALWAYS return a string so setting it to an Integer(mywholeNo) is not correct. Most of the time PowerPoint is fairly clever and converts the input to an integer for you. However enter something non numeric and PowerPoint will complain throwing an eror message and giving the user the option to "fix" the code - Not good!

Here's how to check that a number is entered. Note that the input is declared correctly as a string and only converted to an integer after checking that it's a number..

Sub GetNo()
Dim strwholeNo As String
Do
strwholeNo = InputBox("Enter a whole number")
Loop Until IsNumeric(strwholeNo)
MsgBox "Double this number is " & 2 * CInt(strwholeNo)
End Sub

The only remaining problem is that if the user clicks CANCEL (or the red X) the inputbox will not cancel but ask for more input! This can be fixed simply by checking that strWholeNo is = "" and exiting if it is. Howvere this is not the best way because it does not differentiate between the user clicking OK with no input and a true CANCEL. This is in fact quite difficult to achieve.

Here's how using the hidden method StrPtr

StrPtr (String pointer) gives the address that the string is stored at. You don't need to know this BUT if CANCEL was pressed there is no string and strptr will not exist. You can check for this and know for sure that either CANCEL or the red X was pressed and NOT just a blank input ""

Sub Getuser()
Dim strwholeNo As String
Do
strwholeNo = InputBox("Enter a whole number")
'cancel pressed - exit
If StrPtr(strwholeNo) = False Then Exit Sub
Loop Until IsNumeric(strwholeNo)
MsgBox "Double this number is " & 2 * CInt(strwholeNo)
End Sub

You should use this with string inputs too:

Sub Getuser()
Dim strUser As String
strUser = InputBox("Enter your Name")
If StrPtr(strUser) = False Then Exit Sub
MsgBox "Hello " & strUser
End Sub

Personalise Your InputBox

As wel as specifying the default Prompt message you can easily add a title and default value. Try this and see where the messages appear.

Sub GetNo()
Dim strwholeNo As String
Do
strwholeNo = InputBox _
("Enter a whole number and I will double it", "Number Doubler", "10")
If StrPtr(strwholeNo) = False Then Exit Sub
Loop Until IsNumeric(strwholeNo)
MsgBox "Double this number is " & 2 * CInt(strwholeNo)
End Sub

Back to the Index Page

POWERPOINT BLOG

Articles on your favourite sport

Free Microsoft PowerPoint Advice, help and tutorials, Template Links
This website is sponsored by Technology Trish Ltd
© Technology Trish 2007
Registered in England and Wales No.5780175
PowerPoint® is a registered trademark of the Microsoft Corporation