Workbooks_Open crashes when File opened with code. Manually Open OK. UserForm Issue?

Workbooks_Open crashes when File opened with code. Manually Open OK. UserForm Issue?

Hi
This is ( was ) driving me crazy.
I have ( had ) a large Workboook.
It has a Workbook_Open code in it.
If I open this file manually I have no problem.

If I use a certain code to open this then Excel crashed right at the end of a Workbook_Open code.

I busted my balls for a day stripping the code down until I located what causes this. I also stripped down the File I give here to the minimum in order to demo the problem here and aid in anyone trying to help.

The problem is somehow coming from the UserForms I have. If I remove these , or the code bit referencing them, then the problem is gone.

I need to use a global variable for one of my user form instances in the original code. I suspect this may somehow come into the equation of the problem as well

I am guessing that there is some form of initialisation or clearing which goes on when I close and open manually the code. May be for some strange reason this is not occurring when I close and open the File with a code. So maybe I am missing some code line to do before or after I close the File in the code before the code later re- opens it again. ( For various reasons I do need to open and close my original code. That is not negotiable! – I have just here in the demo codes I give tried to reduce everything to the minimum so as to not confuse the issue )

Some Observations:
_ The code will sometimes work the first time without crashing Excel

_ The problem also appears to go away if I remove UserForm1, which in the demo code is not used at all? So clearly there is some strange initialisation or mixing up of existences of the Userforms which is Chucking a spanner in the works.

Here the stripped down Workbook_Open code which gives the problem. It is in the File “ProAktuellex8600x2WSOpenCrash.xlsm” which I have uploaded. This file also includes the offending UserForms.

Option Explicit
Private Sub Workbook_Open()
On Error GoTo Err
        If fm Is Nothing Then Set fm = New ufResults ' If this line was not yet passed, ( or the UserForm had been manually closed ), then a new instance of the Class ufResults with the name fm is made
        If Not fm.Visible Then fm.Show False 'make sure UserForm is always there,
    fm.OptionButton2.Value = True
    fm.Refresh.Value = True
If Application.Wait(Now + TimeValue("0:00:2")) Then
 MsgBox "Here comes the crash if you are opening the File with a code"
Else
End If
Exit Sub

Err: ' Just in case   ;)
MsgBox Prompt:=Err.Description
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

_....................................__...


Here an example of a code that would cause the problem. The crash actually occurs at the end of the Worksheet_Open code which is initiated in the last line of the below code at the point at which the code below opens the offending File. ( Line 120 ) . The code is also in the uploaded File “NeuProAktuelleMakrosWBOpenCrash.xlsm” There is nothing but that simple code in that File.
I have written the demo codes so that they are stored and accessed from my desktop. So will need to alter that ( Lines 60, 70 and 120 ) to match where you save the files, should you download them.

Option Explicit
Sub SaveProDesktop()
10   WB As Workbook: Set WB = Workbooks("ProAktuellex8600x2WSOpenCrash.xlsm") ' This will typically be open at the start of this code
20  Dim WBFullName As String: Let WBFullName = WB.Name       'Determine it now as later when WB is closed it will not appear in Workbooks Collection
30
40 'save File and close
50  Application.DisplayAlerts = False 'Stop being asked stuff on saving                                                                     ' 'ChDir "C:\Users\Elston\Desktop
60  WB.SaveAs FileName:="C:\Users\Elston\Desktop\" & WBFullName & ""                                            'WB.Save        ' 'Quick in case I added and forgot to save in original Type!!  - I did a lot often
70  WB.SaveAs FileName:="C:\Users\Elston\Desktop\Temp" & Format(Now(), "mmss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook           ' File copy in .xlsx Form
80  Application.DisplayAlerts = True 'Switch it back on
90  WB.Close True 'Close Saved File
100
110 'Open file                                                                                                                                        'ChDir "C:\Users\Elston\Desktop"
120 WB.Open FileName:="C:\Users\Elston\Desktop\" & WBFullName & ""
End Sub

_...........___...

( I get this problem on 2 different computers, one is using XL 2007, the other uses XL 2010 )

Thanks for any help.
Alan.

_.........._______________________--

P.s. Here a screen shot od the relavent things:
Attachment 461303



_...__

P.p.s. Just out of interest the main code, but it is a bit big. – The demo codes replicate the original Problem.
https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0

_..._____________________

Rem Ref http://www.excelforum.com/excel-prog...n-on-open.html
Rem Ref http://www.excelforum.com/excel-prog...on-open-2.html