Can the entire macro be stopped from a UserForm?

Question

Can the entire macro be stopped from a UserForm?

Navigation

#1 by (0 votes)

0

I have a project in VBA in which I am already preparing the outputs for errors and there is an error that can be generated in the initialization of a UserForm .

Private Sub UserForm_Initialize()

    With Application.WorksheetFunction
        On Error GoTo initiate
        'Acá puede ocurrir un error
        nameNUIP.Caption = .VLookup(...) _ 
                   + " " + .VLookup(...) 'Lo pongo así porque no es necesaria la información
        BirthDate.Caption = .VLookup(...)
        RegDate.Caption = .VLookup(...)
        Place.Caption = .VLookup(...) _
               + ", " + .VLookup(...)
        On Error GoTo 0
    End With
Exit Sub

'Control de errores
initiate: 'Acá se viene el código si ocurre el error
    MsgBox Title:="NUIP ERROR:" & Err.Number, _
           Prompt:="Fallo en la búsqueda del NUIP." & vbNewLine & "¿Seguro que ya se registró?", _
           Buttons:=vbOKOnly + vbExclamation
End Sub

When the code passes to the initiate tag by not finding data, the window with the message appears as expected, but after accepting, the macro continues and shows the UserForm , which I do not want. I wish that if the error occurs, stop running the macro, but do not close the book.

Is it possible to close everything if an error occurs? What methods could you use for this?

PS: My version of Excel is 2016.

vba

asked by JuanSeDO_1 04.01.2018 в 22:22

source

1 answer

conversion to a data type? Sort Leaves in xlsx

score 0 · Accepted Answer

I can think of two options:

1. Download the form in the Activate event

Instead of using the event Initialize() "hang" the code of Activate() and in case of error download the form by Unload Me . Unfortunately we can not do the same in Initialize , because in that instance the form has not finished instantiating.

Private Sub UserForm_Activate()

    Dim Dummy As Integer

    On Error GoTo Error
    Dummy = 1 / 0

    Exit Sub

Error:
    MsgBox Title:="NUIP ERROR:" & Err.Number, _
           Prompt:="Fallo en la búsqueda del NUIP." & vbNewLine & "¿Seguro que ya se registró?", _
           Buttons:=vbOKOnly + vbExclamation
    Unload Me

End Sub

The only observable is that the form will open anyway and then close in case of error, if you do not want this, then the next point would be better:

2. Create in a module a routine to open the form and in this do the control you want to do:

Private Sub Load_UserForm1()

    Dim Dummy As Integer

    On Error GoTo Error
    Dummy = 1 / 0

    UserForm1.Show

    Exit Sub

Error:
    MsgBox Title:="NUIP ERROR:" & Err.Number, _
           Prompt:="Fallo en la búsqueda del NUIP." & vbNewLine & "¿Seguro que ya se registró?", _
           Buttons:=vbOKOnly + vbExclamation

End Sub

If there is no error, the form will be opened when invoke UserForm1.Show and in case of error only the error message is displayed. Of course you have to modify the current logic so that the loading of the form is done only by invoking our routine Load_UserForm1