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

Without this being done externally to VBA coding, this wouldn't be possible
given the instantiating issue that you face. This would be something that
would have to be done with some external code to first open a new instance
of Excel, then to open the file within it.

I also have faced a similar issue to this, but rather with the userform
coming up, which then for any other file to be opened up, they would have to
open up a second instance of Excel before they could try to open up any
other file. That is cause for as long as the userform is up for the one
file, no other file could be opened within that instance manually speaking
(from inside or from outside of that instance with the userform up).

Several operators has not liked this issue, and I have shown them numerous
times how to get around the issue, so as they can do other things as needed,
and yet, still be able to use the one file to record their production data.
One thing is, I don't have the code hide the application or anything of that
nature as the one time when I was running a test of that, it cased several
other issues down the road, such as the application not getting closed out
of memory like it should have.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"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

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

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