Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Error in InputBox

  Asked By: Carolina    Date: Sep 20    Category: MS Office    Views: 956
  

I created a macro that will ask for a number then prompts a message. I
defined the variable (val) as string. It works fine when numbers are
entered in the inputbox but a run time error is encountered when
character is entered. Please suggest how to modify the code so that I
can go away with the runtime error when a character (or combination of
numbers and characters) is entered. Below is sample code I have done.

Dim val as String

val = Application.InputBox("Enter any number between 700 to 800")

If val = "" Then
MsgBox "No data entered."

ElseIf val = False Then
MsgBox "No data entered"

ElseIf val >= 700 or val <= 800 Then
MsgBox "OK"

Else
MsgBox "Wrong entry"

End If


Error when a character or combination of number and characters are entered:
Run time error '13':
Type mismatch

Share: 

 

10 Answers Found

 
Answer #1    Answered By: Daw Boonliang     Answered On: Sep 20

Could you please elaborate on what is the objective of your code. What I mean
is whether you would like to let the user enter both text as well as numbers  or
allow the user to enter only numbers?

 
Answer #2    Answered By: Christie Bradley     Answered On: Sep 20

Data I need is number  only but there is a high probability that a user
may enter letters.
So basically I don't want the user to enter any other data other than a
number. If letters (say for example abcdef) or combination  of number and
letters (say for example 1C6336D).

 
Answer #3    Answered By: Wendy Harrison     Answered On: Sep 20

I think the mistake you did is that, you had named your variable  as 'val'. This
is wrong. Because val  is a keyword of Visual Basic and if you've some string  or
text and want to convert the same into a number  (integer) then you can use this
Val function.

For example see the below code.

Dim X as String
Dim Y as Integer
Dim Z as Integer
X=2
Y=3
' Now I want to add X & Y and get the result in Z
Z = X+Y
Msgbox Z

'''' If I do like this then it will give me an error  message stating that 'Type
Mismatch'.

---- The reason is I've defined the variable X as string and adding a String and
an Integer. So comes the error. If both were strings then the result will be 2+3
as such. The '+' (plus) sign will be used automatically by Visual Basic for
concatinating the two strings.

But actually here we've one string and one integer, so it will show an error.

Then you have to do a little modification here, like below in the sumation
point.

Z= Val(X)+Y

' now you will get 5 as the result in the msgbox.

So try changing the variable name and let us know the status of your result or
error.

 
Answer #4    Answered By: Noah Evans     Answered On: Sep 20

I think the mistake you did is that, you had named your variable  as 'val'. This
is wrong. Because val  is a keyword of Visual Basic and if you've some string  or
text and want to convert the same into a number  (integer) then you can use this
Val function.

For example see the below code.

Dim X as String
Dim Y as Integer
Dim Z as Integer
X=2
Y=3
' Now I want to add X & Y and get the result in Z
Z = X+Y
Msgbox Z

'''' If I do like this then it will give me an error  message stating that 'Type
Mismatch'.

---- The reason is I've defined the variable X as string and adding a String and
an Integer. So comes the error. If both were strings then the result will be 2+3
as such. The '+' (plus) sign will be used automatically by Visual Basic for
concatinating the two strings.

But actually here we've one string and one integer, so it will show an error.

Then you have to do a little modification here, like below in the sumation
point.

Z= Val(X)+Y

' now you will get 5 as the result in the msgbox.

So try changing the variable name and let us know the status of your result or
error.

 
Answer #5    Answered By: Candace Foster     Answered On: Sep 20

I was trying to solve your problem with the least amount of code  and came
across a great way to check for number  being entered  in a single line.

Dim val  as integer

val = Application.InputBox("Please enter a number","Number checker", Type:=1)

'Here you cannot enter any thing other than a number

 
Answer #6    Answered By: Jo Fowler     Answered On: Sep 20

I already found solution to my problem (using the IsNumeric) but your
suggestion is great!

 
Answer #7    Answered By: Blaze Fischer     Answered On: Sep 20

I found way to fix the code  using the 'IsNumeric' command. I modified
the code as follows and it's working without the type mismatch error.
(I'll just modify  again to use do while loop as suggested by Neville).

Dim val1 As String

val1 = InputBox("Enter any number  between 700 to 800")
val2 = IsNumeric(val1)

If val1 = "" Then
MsgBox "No data entered."

ElseIf val2 = False Then
MsgBox "Wrong data"

ElseIf val2 = True Then
If val1 >= 700 And val1 <= 800 Then
MsgBox "OK"
Else
MsgBox "Wrong"
End If

End If

 
Answer #8    Answered By: Pam Harrison     Answered On: Sep 20

I tried changing my variable  val to val1 but type mismatch error  still
encountered.
In the code  below, I think the problem lies in the line :

ElseIf val  >= 700 And val <= 800 Then

Because I entered  a letter, reads that letter i entered but it's
comparing to a number. How should I fix this so that if I enter a
letter, it will not compare to the number  of the "if " conditions? I
thought the last "else" command in my code will take care of any other
other input other than the defined conditions in the "if" lines.

 
Answer #9    Answered By: Shannon Hughes     Answered On: Sep 20

Keep coding and enjoy ur programming....

 
Answer #10    Answered By: Clinton Edwards     Answered On: Sep 20

You do not have to use isNumeric nor the do Loop.

I have a method of doing this without using the IsNumeric.

The Application.InputBox method I had stated earlier is a superb way
of getting input in only number  format. If you enter any other format
then an error  will be dispalyed automatically without any need for
further code.

Please use this. To find out how to do it please refer to a previous
posting on the same thread.

 
Didn't find what you were looking for? Find more on Error in InputBox Or get search suggestion and latest updates.




Tagged: