Loading Reference aborts Sub

[Mahahaava]

Hi,

I have a problem: on loading an external reference in the VBE, my Auto_Open routine is cancelled no matter how I try to do it. The reference is loaded but any subsequent commands are not...

OK I tried moving the load to the end but his resulted in my userform to be unloaded ... ie same problem. Any suggestions? Here's me code:

[VBA] Sub Auto_Open()
Dim SaveStatusBar
Dim Oikeudet As String
Dim Versio As String
Application.ScreenUpdating = False
Application.Caption = "Tilat SAMPO PANKKI R 1.04.C"
'Versio = Application.Version
'If Versio <> "11.0" Then
' MsgBox "Excel ei ole versio 2003! Tämä DEMo toimii vain tässä versiossa!!", vbCritical, "Poistutaan sovelluksesta!"
' Exit Sub
'End If
Oikeudet = HaeOikeudet(Environ("USERNAME")) ' Tämä on "sisään loggaustunnus"!
If Oikeudet = "NONE" Then ' Heitetään ulos!
ThisWorkbook.Saved = True
MsgBox "Käyttäjää " & Environ("USERNAME") & _
" ei ole määritelty tilalusovelluksen käyttäjäksi!", , "Seis"
' Korjattu 20.2.2009, sulkee tämän sovelluksen muttei muita avoimia Exleleitä _
jos käyttäjälle ei ole annettu oikeuksia
Workbooks("Tilat SAMPO.xls").Close
Exit Sub
End If
If Oikeudet = "READ" Then
' HaeKatselukäyttäjänAsetukset
Exit Sub
End If
AsetaSuojaukset
SaveStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Sovellus ladataan ..."
Application.StatusBar = False
Application.DisplayStatusBar = SaveStatusBar
With Application
.AlertBeforeOverwriting = True
.ActiveWorkbook.EnableAutoRecover = False
End With
PiilotaSivu "Käyttäjät"
DeletoiExcelinMenut
'SetupPäävalikkoMenu
'PoistaMuutMenut
SetupMenus
ThisWorkbook.Sheets("SAMPO").Activate
Päävalikko.Show
'
'
' The above line loads the userform and the below #¤%& negates it and won't let me load it after the fact either...
'
'
'
If FileThere("c:\Program Files\Common Files\Autodesk Shared\acax17enu.tlb") Then
On Error Resume Next
Application.DisplayAlerts = False
Application.VBE.ActiveVBProject.References.AddFromFile _
"c:\Program Files\Common Files\Autodesk Shared\acax17enu.tlb"
On Error GoTo 0
'
'
'
Else
MsgBox "AutoCAD 2008 ei ole asennettuna... AutoCAD linkit eivät toimi!"
End If
AktivoiSAMPO
End Sub
Sub PiilotaSivu(Sivu As String)
If Workbooks("Tilat SAMPO.xls").Worksheets(Sivu).Visible = True Then
Workbooks("Tilat SAMPO.xls").Worksheets(Sivu).Visible = False
End If
End Sub[/VBA]

[Jan Karel Pieterse]

What you should do is move everything AFTER the code that loads the reference to a new sub. Then immediately BEFORE loading the reference, use Application.Ontime to schedule starting the new sub:

'Previous Auto_Open code
Application.Ontime Now(),"YourNewSubWhichAlsoShowsTheUserform"
'Code setting a new reference goes here

[Mahahaava]

What you should do is move everything AFTER the code that loads the reference to a new sub. Then immediately BEFORE loading the reference, use Application.Ontime to schedule starting the new sub:

'Previous Auto_Open code
Application.Ontime Now(),"YourNewSubWhichAlsoShowsTheUserform"
'Code setting a new reference goes here

Jan,

thank you!

I'll try your solution. In the meantime I have solved the problem by using an external xls to boot the main program like thisin start - or in my case DEMO.xls)

From Auto_Open...(of DEMO.xls)

KäynnistäSovellus 'Starts new application
If Workbooks("Demo.xls").Worksheets(1).Cells(1, 1) <> "DONE" Then
Workbooks("Demo.xls").Worksheets(1).Cells(1, 1).Text = "DONE"
ThisWorkbook.Save 'Saves Starting WB with knowledge of initial Start
 Workbooks("Tilat SAMPO.xls").Close savechanges:=True 'Saves Main program with reference loaded
     Workbooks.Open(FileName:= _
        "C:\Temp\SAMPODEMO\Tilat SAMPO.xls"). _
        RunAutoMacros Which:=xlAutoOpen ' Re-Opens Main program
End If