开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜