How to do error messages

[ahmyax2]

how do u add error message, so that if you do not put the correct type of value in a input box and a error message should come for you to re do it again

error message should not take letters, signs etc onli numbers
and if number 0 is entered it should stopped

 

[wilcowoods]

Hi ahmyax2

would data validation do the job for you? You can select the range you want to validate then click on data on the ribbon (in 2007) then select validation and choose how you want to customise the validation or designate a drop down list.

This is actually a kind of formatting so you'll need some code to prevent dragging of cells and copying and pasting etc.

I have something that works perfectly if you decide to go with it!

Regards
Wilco

 

[bertie]

Hi and welcome to the baord.

There are several ways of doing this, here is one:

[COLOR=darkblue]Sub[/COLOR] test()
 
   myValue = InputBox("Please enter a number greater than zero")
 
   [COLOR=green]'test for the input value not being a number[/COLOR]
   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsNumeric(myValue) [COLOR=darkblue]Then[/COLOR]
      MsgBox myValue & " is not a valid number, please re-enter!"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
   [COLOR=green]'test for input value being greater than zero[/COLOR]
   [COLOR=darkblue]If[/COLOR] myValue = 0 [COLOR=darkblue]Then[/COLOR]
      MsgBox "Please enter a number greater than zero!"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[ahmyax2]

the error message is for vba to work out a formula

 

[ahmyax2]

am getting
"
run time error '13'
type mismatch
"

 

[bertie]

In my code I deliberately did not declare the variable "MyValue" so it would default to type variant.

In your code what type have you declared the variable which is assigned the inputbox value?

 

[bertie]

In the code below the value I expect to be entered is an Integer, but I have to accomodate the user entering something other than an integer. The standard way of doing this is to:

switch off error handling on error resume next

Read in the value converting it to an integer. This is why we switch off error handling, if the value entered is not an integer we would get a type mismatch error

switch on error handling on error goto 0

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=red]Dim myValue As Integer[/COLOR]
 
   [COLOR=red]On Error Resume Next[/COLOR]
 
   myValue = [COLOR=red]CInt[/COLOR](InputBox("Please enter a number greater than zero"))
 
  [COLOR=green]'test for the input value not being a number[/COLOR]
  [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsNumeric(myValue) [COLOR=darkblue]Then[/COLOR]
     MsgBox myValue & " is not a valid number, please re-enter!"
     [COLOR=#ff0000]On Error GoTo 0[/COLOR]
     [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
[COLOR=red]On Error GoTo 0[/COLOR]
 
  [COLOR=green]'test for input value being greater than zero[/COLOR]
  [COLOR=darkblue]If[/COLOR] myValue = 0 [COLOR=darkblue]Then[/COLOR]
     MsgBox "Please enter a number greater than zero!"
     [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
 
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[bertie]

This worked thank you very much but i have find out that when you enter the wrong code the error message comes up and when u click ok, the inputbox closes. How can u make it restart again and let u re enter a final chance before Thank You or let you cancel

Hi Ahmyax2, thanks for the pm.

Unfortunately I did not retain a copy of my reply to your earlier pm therefore had to redo this from scratch. Please post follow-ups to the thread rather than sending pm's.


As we anticipate the user will enter wrong information, Error handling is disabled while input is received, On error Resume Next. And reset to handle errors as normal when all input is received, On Error Goto 0. In between all errors will be handled by the code.

The code below forces the user to enter a numeric value greater than zero. To ensure valid input the user prompt is placed within a Do loop. As such you may wish to consider giving the user a way of ending the procedure, i.e, something like "Enter 0 to Terminate".

[COLOR=darkblue]Sub[/COLOR] Calculate()
   [COLOR=darkblue]Dim[/COLOR] y [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] n [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] z [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] s [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] success [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
   [COLOR=green]'turn off error handling while reading in values[/COLOR]
   [COLOR=green]'the validation code below will control user input[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   [COLOR=green]'get and validate y[/COLOR]
   success = [COLOR=darkblue]False[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] success = [COLOR=darkblue]True[/COLOR]
      y = InputBox("Enter Loan Amount.", "Loan Amount")
      [COLOR=darkblue]If[/COLOR] IsNumeric(y) And y > 0 [COLOR=darkblue]Then[/COLOR]
         success = [COLOR=darkblue]True[/COLOR]
      [COLOR=darkblue]ElseIf[/COLOR] Err.Number <> 0 [COLOR=darkblue]Or[/COLOR] y < 1 [COLOR=darkblue]Then[/COLOR]
         MsgBox "Please Enter a Valid Loan Amount Greater then zero 0"
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
   
   [COLOR=green]'get and validate n[/COLOR]
   success = [COLOR=darkblue]False[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] success = [COLOR=darkblue]True[/COLOR]
      n = InputBox("Please Enter Years to Pay.", "Years to Pay")
      [COLOR=darkblue]If[/COLOR] IsNumeric(n) And n > 0 [COLOR=darkblue]Then[/COLOR]
         success = [COLOR=darkblue]True[/COLOR]
      [COLOR=darkblue]ElseIf[/COLOR] Err.Number <> 0 [COLOR=darkblue]Or[/COLOR] n < 1 [COLOR=darkblue]Then[/COLOR]
         MsgBox "Please Eneter a Valid Number of Years to Pay" & vbCrLf _
               & "Greater then Zero."
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
 
   [COLOR=green]'get and validate r[/COLOR]
   success = [COLOR=darkblue]False[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] success = [COLOR=darkblue]True[/COLOR]
      r = InputBox("Please Enter Interest Rate.", "Interest Rate")
      [COLOR=darkblue]If[/COLOR] IsNumeric(r) And r > 0 [COLOR=darkblue]Then[/COLOR]
         success = [COLOR=darkblue]True[/COLOR]
      [COLOR=darkblue]ElseIf[/COLOR] Err.Number <> 0 [COLOR=darkblue]Or[/COLOR] r < 1 [COLOR=darkblue]Then[/COLOR]
         MsgBox "Please Eneter a Valid Interest Rate" & vbCrLf _
               & "Greater then Zero."
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
   [COLOR=green]'now the input values have been entered and validated[/COLOR]
   [COLOR=green]'reset error handling back to default mode[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
 
   s = 1 * ((1 - ((r / 100) + 1) ^ n)) / (1 - (r / 100 + 1))
   z = (y * ((r / 100) + 1) ^ n) / (12 * s)
 
   MsgBox "The monthly repayment required to pay of the loan is £" & Round(z, 2)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[ahmyax2]

THANKS it worked

 

[ahmyax2]

1