vba error code in form
hey, first thanks to all for answering my other questions. I am extremely new to Excel VBA and some things I just get hung up on. I have a userform (not embedded in a worksheet) and I have a few fields that are for currency (amounts, etc) and if someone inputs a letter it errors after they hit the command button and they lose all info. I need error code to where I can tell them in a msgbox that they should not put characters in a currency field. I don't need it specific to those fields but I don't want them to lose there data when they hit the command button to dum开发者_StackOverflow社区p the data into a spreadsheet.
How can I have them see the error msg, hit the ok button and have it take me right back to the screen without losing the data they have alread entered? Basically give them the opporunity to correct their error but not have to reinput 50 fields?
Thanks
Can't be specific without the actual code, but add error handlers to your code:
Sub SomeRoutine()
Dim stuff
On Error GoTo EH
' Code
Exit Sub
EH:
' Any errors with come here
If Err.Number = <specific errors to trap> Then
MsgBox "Oops..."
'As a debug tools, put a Resume here,
' but be sure to put a break on it,
' and don't leav it in the finished code
Resume
End If
End Sub
As I understand it you want the user to enter numeric numbers only into the text box - right? This is what I normally do.
In a global module add the following function:
Function IFF(c, t, f)
Dim v
If c Then v = t Else v = f
IFF = v
End Function
Then in your textbox_change event add the below:
Private Sub txtAmount_Change()
txtAmount.Text = IFF(IsNumeric(txtAmounto.Text), Val(txtAmount.Text), 0)
End Sub
This will basically put 0 in the box as soon as the user enters an invalid number.
Hope this helps
A slightly different take on error handlers than that given by chris neilsen
Sub SomeRoutine
On Error GoTo ErrHandler 'doesn't matter where you put it
'as long as it's before the code you want to protect
'Dim Stuff
'Do Stuff
ExitRoutine: 'Note the colon(:), which makes this a label
'Any cleanup that you _always_ want
Exit Sub
ErrHandler:
Select Case Err.Number
Case <some error you want to handle specially>
'special handling
Case Else
'default handling, which may include:
Resume ExitRoutine
End Select
Resume
End Sub
Note that that last Resume
will never get hit in normal processing (if you've written your error-handling Cases correctly), but will let you set it as "Next Statement" when you're debugging in Break mode. This is an easy way to see exactly which statement threw the error.
精彩评论