My code copies data from different workbooks into other workbooks. It loops through 12 workbooks into which data is copied, and each of those 12 workbooks opens (and closes) 6 files from which it gets the data.

The first step is that I show the user a UserForm, where they can select a year and a quarter. The code itself works when:

  1. I leave out the Userform and enter the date (= the variables qVar, yVar and fullDate) directly inside the code.

  2. I leave in the Userform, but reduce the number of workbooks from 12 to maybe 7 or so.

If I use the UserForm with all 12 workbooks, I get the

"Automation Error. Exception Occurred."

and Excel shuts down. The UserForm in question consists of a combobox, a frame on which there are 4 option buttons and two command buttons.

Code for the UserForm (qVar, yVar and fullDate are global variables):

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOk_Click()
    Dim QuartalStr As String
    Dim oControl As Control

    If cboJahr.Value = "" Then
        MsgBox "Select year"
        Exit Sub
    End If

    For Each oControl In frmQuartalsauswahl.fraQuartale.Controls
        If oControl.Value = True Then
            qVar = oControl.Caption
        End If
    Next oControl

    yVar = CStr(cboJahr.Value)

    Select Case qVar
        Case "Q1"
            fullDate = yVar & ".03.31"
        Case "Q2"
            fullDate = yVar & ".06.30"
        Case "Q3"
            fullDate = yVar & ".09.30"
        Case "Q4"
            fullDate = yVar & ".12.31"
    End Select

Unload Me
Call QuarterlyReport.WithUserForm
End Sub


Private Sub UserForm_Initialize()
    Dim yearsArray() As Integer
    Dim startyear As Integer
    Dim i As Integer

    startyear = 2017
    i = 0

    Do While startyear <= Year(Date)
        ReDim Preserve yearsArray(i)
        yearsArray(i) = startyear
        startyear = startyear + 1
        i = i + 1
    Loop
    cboJahr.List = yearsArray  
End Sub

Does anyone have an idea what I should do or what I should look for?

edit: Ok, now I got a new error: Excel crashes with the classic error message: "Microsoft Excel has stopped working". One time Excel restored the updated workbooks, which told me: That time it occured for the 10th of the 12 workbooks, after the data from 5 of the 6 source files has been copied. However, that's the same code as for the other workbooks. Anyway, ever since Excel doesn't restore anything but the original files - either that's an issue with Excel or it means my code crashes without altering the files. Without the userform the code still works without any issues.

0 Answers