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:
I leave out the Userform and enter the date (= the variables
fullDate) directly inside the code.
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 (
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.