Force a File to Open in a New Excel Instance
Thanks Peter,
That is right along the lines of what I was thinking. I really appreciate
your help.
Brandt
"Peter T" wrote:
Sounds like a dreadful arrangement. What you ask can be done though but
don't send a file to me that includes the following !
' normal module
Sub auto_open()
'Stop ' for testing
Application.Visible = True ' for testing
If Application.Workbooks.Count = 1 Then
Application.OnTime Now, "ShowForm"
Else
With CreateObject("excel.application")
.Visible = True
.DisplayAlerts = False
Call .Workbooks.Open(ThisWorkbook.FullName, , True)
.ActiveWorkbook.RunAutoMacros xlAutoOpen
End With
ThisWorkbook.Close False
End If
End Sub
Sub ShowForm()
UserForm1.Show
'Stop ' for testing
Application.Quit
End Sub
' userform code
Private Sub UserForm_Activate()
Dim s As String
s = Me.Caption
Me.Caption = "somethingUnique"
AppActivate Me.Caption
Me.Caption = s
End Sub
Private Sub UserForm_Initialize()
Application.Visible = False
End Sub
Private Sub UserForm_Terminate()
Application.Visible = True 'for testing
End Sub
The above is quickly cobbled together and only lightly tested.
At first uncomment those Stops, later re-comment and also the lines that
make the instance visible. If want to open the wb with code but don't want
to run the open event, hold Shift. Ensure multiple wb's are open in the
primary instance for testing..
You might want to find examples in this ng to include something so you can
reactive the form when it gets hidden.
Regards,
Peter T
"Brandt" wrote in message
...
I have a file that, when opened, hides the main excel window and shows a
userform and runs some code. The form is intended to be left open for
extended periods of time. This becomes problematic if the user opens the
file when they already have another excel file open because my file hides
their file until they close the userform. I have tried various things to
get
around this, but what I would really like to do is have my file first
check
to see if it is the only file open in the current instance, and if not
"re-open" itself in a new instance before the rest of the code is run. Is
this possible to have a file close itself in one instance and open itself
in
another one?
Thanks for any help
Brandt
|