Handling Error '13' in Excel VBA (Variables and Text In Spanish)

Question

I'm creating a macro to help the user delete a row through a textBox. The user needs to click on the Eliminar hallazgo (Delete Finding) button.

Then, a form with an empty text box appears for the user to write the number of the row that will be deleted.

If a user writes an Int, the Macro runs OK (I have no idea on how to optimize it). But when a user writes something different the Macro crashes.

I would like to have a MsgBox popping up that says something like "Please enter a valid number" whenever the user writes anything different.

So far, this is my code:

Private Sub AceptarButton_Click()

Dim row2Empty As Integer, rangoDel As Range

' Asignar valor escrito por usuario a Variable

row2Empty = TextFila.Value
On Error GoTo InvalidValue:
Exit Sub

Worksheets(5).Activate


If TextFila.Value = True Then
    Worksheets(5).Rows(row2Empty).Select
    Worksheets(5).Rows(row2Empty).Delete
End If

Unload Me

InvalidValue:
    MsgBox "Por favor digite un numero."
Resume Next

End Sub

I'm really confused on how to handle that error.

Thanks in advance,

Jaime

Side note, [use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, Feb 20 '20 at 16:45
You can use [`IsNumeric`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/isnumeric-function) to validate the input. — BigBen, Feb 20 '20 at 16:46

Brian M Stafford · Accepted Answer · 2020-02-20T17:32:07.833

4

The cleanest way to handle this requirement is to not allow entry of anything other than a numeric digit. You can do this by responding to the KeyPress event:

Private Sub TextFila_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   'allow only numeric digits and the backspace key
   If (KeyAscii < 48 Or KeyAscii > 57) And KeyAscii <> 8 Then KeyAscii = 0
End Sub

This approach allows you to simplify the logic in the button click event, too.

edited Feb 20 '20 at 17:32

answered Feb 20 '20 at 16:56

Brian M Stafford

8,483
2
16
25

1

This. Don't annoy the user with obnoxious message boxes - *prevent* invalid inputs instead. Note: it gets a little bit more complicated when you need to allow decimals, but for integer inputs this is exactly ideal. – Mathieu Guindon Feb 20 '20 at 16:57
I added what you suggested, but now I get a Compilation Error. I'm making something wrong, but I don't know what. This is my code: `Private Sub UserForm_Initialize() 'Empty TextFila TextFila.Value = "" End Sub Private Sub TextFila_KeyPress(KeyAscii As Integer) 'allow only numeric digits and the backspace key If (KeyAscii < 48 Or KeyAscii > 57) And KeyAscii <> 8 Then KeyAscii = 0 End Sub Private Sub AceptarButton_Click() Dim row2Empty As Integer ' Asignar valor a var row2Empty = TextFila.Value Worksheets(5).Activate Worksheets(5).Rows(row2Empty).Delete Unload Me End Sub` – Jaime Aluja Feb 20 '20 at 17:11
@JaimeAluja I updated my post to use the correct signature for the KeyPress event. Give it a try. – Brian M Stafford Feb 20 '20 at 17:32

Ricardo Diaz · Answer 2 · 2020-02-20T17:29:52.660

EDIT: Mathieu and Brian are right about the cleaniest approach. Will leave my answer as an illustration.

No need for the userform.

You can do it directly with an inputbox.

Read the code's comments and adjust it to fit your needs.

Private Sub AceptarButton_Click()

    Dim targetSheet As Worksheet
    Dim row2Delete As Variant
    Dim range2Delete As Range


    ' Define the target sheet where row is going to be deleted
    Set targetSheet = ThisWorkbook.Worksheets(1)

    ' Loop until enter a valid number or cancel
    Do While True

        ' Get user value
        row2Delete = InputBox("Por favor indique cuál es la fila a eliminar", "Confirmar fila a eliminar")

        ' Check
        Select Case True
        Case StrPtr(row2Delete) = 0
            ' User canceled
            Exit Sub
        Case Not IsNumeric(row2Delete)
            ' Not a number, alert and ask again
            MsgBox "Por favor digite un numero."
        Case IsNumeric(row2Delete)
            ' Is a number, continue
            Exit Do
        End Select
    Loop

    ' Delete row in sheet index = 5
    targetSheet.Rows(row2Delete).Delete

End Sub

Let me know if it works

At what line? targetSheet should be 5 -> `Set targetSheet = ThisWorkbook.Worksheets(5)` — Ricardo Diaz, Feb 20 '20 at 17:23
That worked like a charm. I just added an `Unload Me` before the `End Sub` line — Jaime Aluja, Feb 20 '20 at 17:30

Handling Error '13' in Excel VBA (Variables and Text In Spanish)

2 Answers2