CODEWITHSUNDEEP
vbaexceluserform
mariu5
mariu5

Reputation: 455

VBA: Exiting a macro during runtime via userform button results in error 429 when restarting the macro

I have macro in Excel which extracts data from two other workbooks and imports it. During this process, a progress bar is shown and the user can quit at any time via a cancel and the standard X button.

When I quit the process using any of these two buttons, it gives me the error 429 when I try to start the macro again. Somehow my form is still active I guess. After I press the reset button in the VBA editor, I can start the macro again without any errors.

I also have an OK button on my form which becomes active when the import process is completely finished.

All of these three buttons execute the same code snippet which is closing everything. The only difference is that they are used at different points of the execution which makes it a little confusing to debug.

My progress bar:

progress bar

Code in userform:

Dim Button As String
Sub StartForm(CalledFrom As String)

    Button = CalledFrom

End Sub

Private Sub UserForm_Initialize()

    ProgressBar.PBOKButton.Enabled = False

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = 0 Then

        If ProgressBar.PBBar.Width < 200 Then

            If MsgBox("Wollen Sie den Vorgang wirklich abbrechen?", vbYesNo + vbQuestion, "Extrahiere Daten...") = vbNo Then

                'Do nothing
                Cancel = True

            Else

                ExitProcess

            End If

        Else

            ExitProcess

        End If

    End If

End Sub

Private Sub UserForm_Activate()

    If Button = "GetDataButton" Then

        GetData

    End If

End Sub

Private Sub PBOKButton_Click()

    ExitProcess

End Sub

Private Sub PBCancelButton_Click()

    If MsgBox("Wollen Sie den Vorgang wirklich abbrechen?", vbYesNo + vbQuestion, "Extrahiere Daten...") = vbYes Then

        ExitProcess

    End If

End Sub

Code snippet to end it all (stored in my macro)

Sub ExitProcess()

    KostenstellenWB.Close SaveChanges:=False

    MAStundenWB.Close SaveChanges:=False

    ExcelApp.Quit

    Set ExcelApp = Nothing

    End

End Sub

Error

error

Thanks for any help.

Upvotes: 2

Views: 1187

Answers (2)

mariu5
mariu5

Reputation: 455

I solved it by moving the sub routine ExitProcess into the userform code and removed every Unload Me. Now when the user quits via cancel button or top right x button only the routine ExitProcess gets executed.

I was able to restart the process without any errors after that.

Sub ExitProcess()

    KostenstellenWB.Close SaveChanges:=False

    MAStundenWB.Close SaveChanges:=False

    ERWB.Close SaveChanges:=False

    ExcelApp.Quit

    Set ExcelApp = Nothing

    End

End Sub

Private Sub UserForm_Initialize()

    ProgressBarForm.PBOKButton.Enabled = False

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = 0 Then

        If ProgressBarForm.PBBar.Width < 200 Then

            If MsgBox("Wollen Sie den Vorgang wirklich abbrechen?", vbYesNo + vbQuestion, "Extrahiere Daten...") = vbNo Then

                'Do nothing
                Cancel = True

            Else

                ExitProcess

            End If

        Else

            ExitProcess

        End If

    End If

End Sub

Private Sub UserForm_Activate()

    GetData

End Sub

Private Sub PBOKButton_Click()

    ExitProcess

End Sub

Private Sub PBCancelButton_Click()

    If MsgBox("Wollen Sie den Vorgang wirklich abbrechen?", vbYesNo + vbQuestion, "Extrahiere Daten...") = vbYes Then

        ExitProcess

    End If

End Sub

Upvotes: 0

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

I once had similar problems in Word VBA and, although I don't remeber nor can find all the details, the solution was somewhere like the following:

' In module myUserForm
'
Dim Button As String
Dim myCancel as Boolean
Sub StartForm(CalledFrom As String)
    Button = CalledFrom
    myCancel= False
    Me.Show
    ' Control returns after the form has been unloaded
    If (myCancel = True) Then
        ExitProcess
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        If ProgressBar.PBBar.Width < 200 Then
            If MsgBox("Wollen Sie den Vorgang wirklich abbrechen?", vbYesNo + vbQuestion, "Extrahiere Daten...") = vbNo Then
                'Do nothing
                Cancel = True
            Else
                myCancel = True
                Unload Me                  ' unload the form from memory
            End If
        Else
                myCancel = True
                Unload Me                  ' unload the form from memory
        End If
    End If
End Sub

That is, ExitProcess is run once the form has been unloaded. Note that this still occurs within the user form module. If this doesn't work, then you have to move the ExitProcess function to the caller of StartForm, which I assume is outside the userform module so you are sure nothing of the module is being used anymore.

Upvotes: 0

Related Questions