Error handling VBA

[Deuce Bucksman]

Hello gurus.
Quick question: i'm trying to set up the inputs for the input boxes to send a message box and close the sub if the user enters anything that isn't a number (zero or text). I tried to use an onerror statement and set up a label below the sub, but i think i'm missing some key steps. If anyone can please give me some guidance on how to handle the error handling, I'd be very appreciative. Thanks so much for your help!



Private Sub CommandButton1_Click()
Dim principal As Variant
Dim interestrate As Variant
Dim payment As Variant
Dim startdate As Variant
Dim reply1 As Variant
Dim reply2 As Variant
Dim reply3 As Variant

reply1 = MsgBox("Hello, " & Application.UserName & "!" & " Would you like to change the amortization projection?", vbYesNo, "Amortization")
If reply1 = vbNo Then Exit Sub Else
GoTo amortization

amortization:
principal = Abs(InputBox("Please enter the total amount you'd like financed", "Principal Balance"))
On Error GoTo badentry
Exit Sub

interestrate = InputBox("Please enter the rate that you think you can get.", "Interest Rate")
payment = WorksheetFunction.Pmt(payment, interestrate, 360, 0)
reply2 = MsgBox("The monthly payment is " & Format(payment, "#,###") & "." & " Is this what you're looking for?", vbYesNo, "Payment")
If reply2 = vbYes Then
ActiveSheet.Range("A12") = payment
Else
MsgBox ("Might want to try again. Pretty sure I did everything correctly.")
Exit Sub
End If
badentry:
MsgBox ("bad entry")

End Sub

 

[JackDanIce]

I'm not too sure about how you're making your code jump to different parts, some are not needed. Maybe try:

Private Sub CommandButton1_Click()
Dim principal As Variant
Dim interestrate As Variant
Dim payment As Variant
Dim startdate As Variant
Dim reply1 As String, reply2 As String, reply3 As String
 
reply1 = MsgBox("Hello, " & Application.UserName & "!" & " Would you like to change the amortization projection?", vbYesNo, "Amortization")
 
If reply1 = vbNo Then Exit Sub
 
principal = Abs(InputBox("Please enter the total amount you'd like financed", "Principal Balance"))
On Error GoTo badentry
' Exit Sub   <- If there isn't an error, it won't jump to Badentry but exit sub
 
interestrate = InputBox("Please enter the rate that you think you can get:", "Interest Rate")
payment = WorksheetFunction.Pmt(payment, interestrate, 360, 0)
 
reply2 = MsgBox("The monthly payment is " & Format(payment, "#,###") & "." & " Is this what you're looking for?", vbYesNo, "Payment")
 
If reply2 = vbYes Then
  ActiveSheet.Range("A12") = payment
Else
  MsgBox ("Might want to try again. Pretty sure I did everything correctly")
  Exit Sub
End If
 
badentry:
MsgBox ("Bad entry")
End Sub

 

[Deuce Bucksman]

Thanks for the quick reply. It still doesnt work. If I enter nothing, or text it just gives me a fail message. I'm trying to be able to control all input without having to use a userform since I'm only asking for two things. I am preplexed.

 

[xenou]

Input boxes give back text so it's almost impossible to enter something that would qualify as a "error".

What you need to do is validate the response. You could also perform other checks here (interest rate that is less than 1, or convert amount to absolute value).

Sample code:

Sub Foo()

Dim rsp As String
Dim principalBalance As Double
Dim interestRate As Double

rsp = InputBox("Please enter the total amount you'd like financed", "Principal Balance")

If IsNumeric(rsp) Then
   principalBalance = CDbl(rsp)
Else
   MsgBox "Invalid Amount."
   Exit Sub
End If

rsp = InputBox("Please enter the rate that you think you can get:", "Interest Rate")
If IsNumeric(rsp) Then
   interestRate = CDbl(rsp)
Else
   MsgBox "Invalid Rate."
   Exit Sub
End If

'//Do something with Amount and Rate ...

End Sub

 

[Deuce Bucksman]

Thanks, all! I took a hybrid of all your input and the end product is a successful procedure. I ended up putting the badentry goto statement at the very beginning of the procedure. It only gives one generic statement if there is an error, but that's really all I need. The end user for this spreadsheet will be me and a few other people so this works. Thanks again.


Xenou, I owe you buddy! You always seem to be there when I'm in a bind.