Running 2 projects at same time issue

I need to run two Excel applications. Each has a different name and different
content. The problem is that when I open a Userform in one, and I click to
select the second application, the Userform of the first application
continues to show. While the Userform in one continues to be active, I cannot
work or open the Userforms of the other.

Has anyone ever experience this issue? How do I bypass it?

Note: this does not seem to have anything to do with wether the Userform is
Modal or Modaless

Edward,
Whilst you can have multiple workbooks open in the same instance of Excel,
only one of them can be active at a time.
Also only one thread of code can execute at a time.

Depending what you are trying to achieve, you hide/show userforms in
another's Activate/Deactivate event or in the same events for the
worksheets.
Obviously, if you are showing a userform vbModal (the default value), you
cannot interact with other objects until you have hidden or unloaded that
form.

NickHK

"Edward" <Edward@discussions.microsoft.com> wrote in message
news:0D238EB8-B64A-464B-B263-514321C98857@microsoft.com...
> I need to run two Excel applications. Each has a different name and
different
> content. The problem is that when I open a Userform in one, and I click to
> select the second application, the Userform of the first application
> continues to show. While the Userform in one continues to be active, I
cannot
> work or open the Userforms of the other.
>
> Has anyone ever experience this issue? How do I bypass it?
>
> Note: this does not seem to have anything to do with wether the Userform
is
> Modal or Modaless

Again, let me state that this problem does not seem to have anything to do
with whether the Userform is Modal.

Try this:

1 - create two Excel files, call them Model 1 and Model 2
2 - add an userform to each UserForm1 and UserForm2
3 - create a simple macro in each Project to open these userforms using the
Show method
4 - set each userform to be Modaless

5 - having both workbooks Model 1 and Model 2 open, open UserForm1
6 - then open UserForm2

Both UserForm1 and UserForm2 should show on the screen regarless of which
Workbook you are making the active one.

I don't the want this behaviour. Other programs such Access don't behave
like this. Does anyone know how to make Excel only show UserForm1 when Model
1 is the active workbook, and to only show UserForm2 when Model 2 is the
active one.

Ed



"NickHK" wrote:

> Edward,
> Whilst you can have multiple workbooks open in the same instance of Excel,
> only one of them can be active at a time.
> Also only one thread of code can execute at a time.
>
> Depending what you are trying to achieve, you hide/show userforms in
> another's Activate/Deactivate event or in the same events for the
> worksheets.
> Obviously, if you are showing a userform vbModal (the default value), you
> cannot interact with other objects until you have hidden or unloaded that
> form.
>
> NickHK
>
> "Edward" <Edward@discussions.microsoft.com> wrote in message
> news:0D238EB8-B64A-464B-B263-514321C98857@microsoft.com...
> > I need to run two Excel applications. Each has a different name and
> different
> > content. The problem is that when I open a Userform in one, and I click to
> > select the second application, the Userform of the first application
> > continues to show. While the Userform in one continues to be active, I
> cannot
> > work or open the Userforms of the other.
> >
> > Has anyone ever experience this issue? How do I bypass it?
> >
> > Note: this does not seem to have anything to do with wether the Userform
> is
> > Modal or Modaless
>
>
>

Difficult to know how Nick could have given any clearer information than he
did on the basis of your OP, catered for scenarios you didn't previously
clarify, including the one you have now.

> Again, let me state that this problem does not seem to have anything to do
> with whether the Userform is Modal.

It has everything to do wither whether the forms are modal/modeless. With a
modal form the only way you could activate another workbook or run a form in
another workbook is via code in the form, say activated from a button.

For your situation with two modeless forms try running same in two workbooks
(pretty much along the lines already suggested) -

'normal module
Public gbUF1 As Boolean
Sub showFormModeless()
UserForm1.Show vbModeless
End Sub

'userform1
Private Sub UserForm_Initialize()
Me.Caption = ThisWorkbook.Name
ThisWorkbook.Activate
gbUF1 = True
End Sub

Private Sub UserForm_Terminate()
gbUF1 = False
End Sub

'thisworkbook module
Private Sub Workbook_Activate()
If gbUF1 Then UserForm1.Show vbModeless
End Sub

Private Sub Workbook_Deactivate()
If gbUF1 Then UserForm1.Hide
End Sub

Various other ways depending on what you are doing.

Regards,
Peter T


"Edward" <Edward@discussions.microsoft.com> wrote in message
news:169AC60E-A07D-4C9A-A0B4-1685592D18B9@microsoft.com...
> Again, let me state that this problem does not seem to have anything to do
> with whether the Userform is Modal.
>
> Try this:
>
> 1 - create two Excel files, call them Model 1 and Model 2
> 2 - add an userform to each UserForm1 and UserForm2
> 3 - create a simple macro in each Project to open these userforms using
the
> Show method
> 4 - set each userform to be Modaless
>
> 5 - having both workbooks Model 1 and Model 2 open, open UserForm1
> 6 - then open UserForm2
>
> Both UserForm1 and UserForm2 should show on the screen regarless of which
> Workbook you are making the active one.
>
> I don't the want this behaviour. Other programs such Access don't behave
> like this. Does anyone know how to make Excel only show UserForm1 when
Model
> 1 is the active workbook, and to only show UserForm2 when Model 2 is the
> active one.
>
> Ed
>
>
>
> "NickHK" wrote:
>
> > Edward,
> > Whilst you can have multiple workbooks open in the same instance of
Excel,
> > only one of them can be active at a time.
> > Also only one thread of code can execute at a time.
> >
> > Depending what you are trying to achieve, you hide/show userforms in
> > another's Activate/Deactivate event or in the same events for the
> > worksheets.
> > Obviously, if you are showing a userform vbModal (the default value),
you
> > cannot interact with other objects until you have hidden or unloaded
that
> > form.
> >
> > NickHK
> >
> > "Edward" <Edward@discussions.microsoft.com> wrote in message
> > news:0D238EB8-B64A-464B-B263-514321C98857@microsoft.com...
> > > I need to run two Excel applications. Each has a different name and
> > different
> > > content. The problem is that when I open a Userform in one, and I
click to
> > > select the second application, the Userform of the first application
> > > continues to show. While the Userform in one continues to be active, I
> > cannot
> > > work or open the Userforms of the other.
> > >
> > > Has anyone ever experience this issue? How do I bypass it?
> > >
> > > Note: this does not seem to have anything to do with wether the
Userform
> > is
> > > Modal or Modaless
> >
> >
> >

Please post solutions only. Don't waste time telling me that I didn't explain
the problem the first time.

Ed

"Peter T" wrote:

> Difficult to know how Nick could have given any clearer information than he
> did on the basis of your OP, catered for scenarios you didn't previously
> clarify, including the one you have now.
>
> > Again, let me state that this problem does not seem to have anything to do
> > with whether the Userform is Modal.
>
> It has everything to do wither whether the forms are modal/modeless. With a
> modal form the only way you could activate another workbook or run a form in
> another workbook is via code in the form, say activated from a button.
>
> For your situation with two modeless forms try running same in two workbooks
> (pretty much along the lines already suggested) -
>
> 'normal module
> Public gbUF1 As Boolean
> Sub showFormModeless()
> UserForm1.Show vbModeless
> End Sub
>
> 'userform1
> Private Sub UserForm_Initialize()
> Me.Caption = ThisWorkbook.Name
> ThisWorkbook.Activate
> gbUF1 = True
> End Sub
>
> Private Sub UserForm_Terminate()
> gbUF1 = False
> End Sub
>
> 'thisworkbook module
> Private Sub Workbook_Activate()
> If gbUF1 Then UserForm1.Show vbModeless
> End Sub
>
> Private Sub Workbook_Deactivate()
> If gbUF1 Then UserForm1.Hide
> End Sub
>
> Various other ways depending on what you are doing.
>
> Regards,
> Peter T
>
>
> "Edward" <Edward@discussions.microsoft.com> wrote in message
> news:169AC60E-A07D-4C9A-A0B4-1685592D18B9@microsoft.com...
> > Again, let me state that this problem does not seem to have anything to do
> > with whether the Userform is Modal.
> >
> > Try this:
> >
> > 1 - create two Excel files, call them Model 1 and Model 2
> > 2 - add an userform to each UserForm1 and UserForm2
> > 3 - create a simple macro in each Project to open these userforms using
> the
> > Show method
> > 4 - set each userform to be Modaless
> >
> > 5 - having both workbooks Model 1 and Model 2 open, open UserForm1
> > 6 - then open UserForm2
> >
> > Both UserForm1 and UserForm2 should show on the screen regarless of which
> > Workbook you are making the active one.
> >
> > I don't the want this behaviour. Other programs such Access don't behave
> > like this. Does anyone know how to make Excel only show UserForm1 when
> Model
> > 1 is the active workbook, and to only show UserForm2 when Model 2 is the
> > active one.
> >
> > Ed
> >
> >
> >
> > "NickHK" wrote:
> >
> > > Edward,
> > > Whilst you can have multiple workbooks open in the same instance of
> Excel,
> > > only one of them can be active at a time.
> > > Also only one thread of code can execute at a time.
> > >
> > > Depending what you are trying to achieve, you hide/show userforms in
> > > another's Activate/Deactivate event or in the same events for the
> > > worksheets.
> > > Obviously, if you are showing a userform vbModal (the default value),
> you
> > > cannot interact with other objects until you have hidden or unloaded
> that
> > > form.
> > >
> > > NickHK
> > >
> > > "Edward" <Edward@discussions.microsoft.com> wrote in message
> > > news:0D238EB8-B64A-464B-B263-514321C98857@microsoft.com...
> > > > I need to run two Excel applications. Each has a different name and
> > > different
> > > > content. The problem is that when I open a Userform in one, and I
> click to
> > > > select the second application, the Userform of the first application
> > > > continues to show. While the Userform in one continues to be active, I
> > > cannot
> > > > work or open the Userforms of the other.
> > > >
> > > > Has anyone ever experience this issue? How do I bypass it?
> > > >
> > > > Note: this does not seem to have anything to do with wether the
> Userform
> > > is
> > > > Modal or Modaless
> > >
> > >
> > >
>
>
>

Should anyone else wish to address your issue, for their and your benefit,
you might explain why the descriptions given both by Nick and myself were
not relevant re use of modal forms. Also explain why the *solution* I gave
you to cater for -

> > > 4 - set each userform to be Modaless
> > > and only showing one form

didn't work or why it cannot be adapted.

Regards,
Peter T

"Edward" <Edward@discussions.microsoft.com> wrote in message
news:B7D6BDEA-FEF6-4563-88DF-7DDF7DCA6E9F@microsoft.com...
> Please post solutions only. Don't waste time telling me that I didn't
explain
> the problem the first time.
>
> Ed
>
> "Peter T" wrote:
>
> > Difficult to know how Nick could have given any clearer information than
he
> > did on the basis of your OP, catered for scenarios you didn't previously
> > clarify, including the one you have now.
> >
> > > Again, let me state that this problem does not seem to have anything
to do
> > > with whether the Userform is Modal.
> >
> > It has everything to do wither whether the forms are modal/modeless.
With a
> > modal form the only way you could activate another workbook or run a
form in
> > another workbook is via code in the form, say activated from a button.
> >
> > For your situation with two modeless forms try running same in two
workbooks
> > (pretty much along the lines already suggested) -
> >
> > 'normal module
> > Public gbUF1 As Boolean
> > Sub showFormModeless()
> > UserForm1.Show vbModeless
> > End Sub
> >
> > 'userform1
> > Private Sub UserForm_Initialize()
> > Me.Caption = ThisWorkbook.Name
> > ThisWorkbook.Activate
> > gbUF1 = True
> > End Sub
> >
> > Private Sub UserForm_Terminate()
> > gbUF1 = False
> > End Sub
> >
> > 'thisworkbook module
> > Private Sub Workbook_Activate()
> > If gbUF1 Then UserForm1.Show vbModeless
> > End Sub
> >
> > Private Sub Workbook_Deactivate()
> > If gbUF1 Then UserForm1.Hide
> > End Sub
> >
> > Various other ways depending on what you are doing.
> >
> > Regards,
> > Peter T
> >
> >
> > "Edward" <Edward@discussions.microsoft.com> wrote in message
> > news:169AC60E-A07D-4C9A-A0B4-1685592D18B9@microsoft.com...
> > > Again, let me state that this problem does not seem to have anything
to do
> > > with whether the Userform is Modal.
> > >
> > > Try this:
> > >
> > > 1 - create two Excel files, call them Model 1 and Model 2
> > > 2 - add an userform to each UserForm1 and UserForm2
> > > 3 - create a simple macro in each Project to open these userforms
using
> > the
> > > Show method
> > > 4 - set each userform to be Modaless
> > >
> > > 5 - having both workbooks Model 1 and Model 2 open, open UserForm1
> > > 6 - then open UserForm2
> > >
> > > Both UserForm1 and UserForm2 should show on the screen regarless of
which
> > > Workbook you are making the active one.
> > >
> > > I don't the want this behaviour. Other programs such Access don't
behave
> > > like this. Does anyone know how to make Excel only show UserForm1 when
> > Model
> > > 1 is the active workbook, and to only show UserForm2 when Model 2 is
the
> > > active one.
> > >
> > > Ed
> > >
> > >
> > >
> > > "NickHK" wrote:
> > >
> > > > Edward,
> > > > Whilst you can have multiple workbooks open in the same instance of
> > Excel,
> > > > only one of them can be active at a time.
> > > > Also only one thread of code can execute at a time.
> > > >
> > > > Depending what you are trying to achieve, you hide/show userforms in
> > > > another's Activate/Deactivate event or in the same events for the
> > > > worksheets.
> > > > Obviously, if you are showing a userform vbModal (the default
value),
> > you
> > > > cannot interact with other objects until you have hidden or unloaded
> > that
> > > > form.
> > > >
> > > > NickHK
> > > >
> > > > "Edward" <Edward@discussions.microsoft.com> wrote in message
> > > > news:0D238EB8-B64A-464B-B263-514321C98857@microsoft.com...
> > > > > I need to run two Excel applications. Each has a different name
and
> > > > different
> > > > > content. The problem is that when I open a Userform in one, and I
> > click to
> > > > > select the second application, the Userform of the first
application
> > > > > continues to show. While the Userform in one continues to be
active, I
> > > > cannot
> > > > > work or open the Userforms of the other.
> > > > >
> > > > > Has anyone ever experience this issue? How do I bypass it?
> > > > >
> > > > > Note: this does not seem to have anything to do with wether the
> > Userform
> > > > is
> > > > > Modal or Modaless
> > > >
> > > >
> > > >
> >
> >
> >

Thanks for your help. I didn't see your suggestions that you had posted.
Still, it is not the anwser I'm looking for.

Let me explain a little more from what I have found out since I posted this
discussion.

Scenario 1:

1 - We have 2 Excel files: Model 1 and Model 2
2 - Using the main Excel application shortcut (no double clicking on file
name) I open Model 1, and repeat same steps to open Model 2 (remember no
double clicking on file names)
3 - Run a macro that opens a Userform in Model 1
4 - Userform in Model 1 opens
5 - Select, Model 2 from the task bar, Userform from Model 1 disappears
6 - Run a macro that opens a Userform in Model 2
7 - Userform in Model 2 opens
8 - Selecting either Model 1 or Model 2 in the taskbar displays their
corresponding Userforms


Senario 2:

1 - We have the same files as in Scenario 1
2 - We open each file (Model 1 and Model 2) by double-clicking the file name
or from the Open command in Excel
3 - We run the macros to open the Userform on each Workbook
4 - Now, the open Userforms behave completely different, now both Userforms
show regardless of which Workbook is active

Does anyone know why this happens? How can I have Scenario 1 execute
regarless of how the files are opened? I don't want to use gimmicks of Hide
here and Show there depending on which Workbook is open because my users
might end up opening more then just 2 models.

Ed

You have taken me (pleasantly) by surprise by responding in a different
manner. But, even though the previous answers are not what you are looking
for they remain the correct and complete.

In your scenario 1 you are opening files in multiple and totally unconnected
Excel instances, so obviously your issue doesn't arise, each only has one
modal form running (assuming of course you don't then open another file in
one of these).

A partial solution at best might be to check Ignore Remote Requests in
Tools, Options, General. Double clicking a file in explorer would open in a
new Excel instance. But that wouldn't solve the problem of opening a second
file in a running instance.

Not sure how you manage to do scenario 2 step 3 a second time with one modal
form already running.

Regards,
Peter T

"Edward" <Edward@discussions.microsoft.com> wrote in message
news:63725541-1905-4934-BFF0-BCC29184D3C5@microsoft.com...
> Thanks for your help. I didn't see your suggestions that you had posted.
> Still, it is not the anwser I'm looking for.
>
> Let me explain a little more from what I have found out since I posted
this
> discussion.
>
> Scenario 1:
>
> 1 - We have 2 Excel files: Model 1 and Model 2
> 2 - Using the main Excel application shortcut (no double clicking on file
> name) I open Model 1, and repeat same steps to open Model 2 (remember no
> double clicking on file names)
> 3 - Run a macro that opens a Userform in Model 1
> 4 - Userform in Model 1 opens
> 5 - Select, Model 2 from the task bar, Userform from Model 1 disappears
> 6 - Run a macro that opens a Userform in Model 2
> 7 - Userform in Model 2 opens
> 8 - Selecting either Model 1 or Model 2 in the taskbar displays their
> corresponding Userforms
>
>
> Senario 2:
>
> 1 - We have the same files as in Scenario 1
> 2 - We open each file (Model 1 and Model 2) by double-clicking the file
name
> or from the Open command in Excel
> 3 - We run the macros to open the Userform on each Workbook
> 4 - Now, the open Userforms behave completely different, now both
Userforms
> show regardless of which Workbook is active
>
> Does anyone know why this happens? How can I have Scenario 1 execute
> regarless of how the files are opened? I don't want to use gimmicks of
Hide
> here and Show there depending on which Workbook is open because my users
> might end up opening more then just 2 models.
>
> Ed

Well, it appears that when you open a Workbook in Excel for the first time it
opens it in an instance of Excel. Subsequent Workbooks that are opened, are
opened in the same instance of Excel. This causes problems when running
macros since there is only one thread of execution.

This can be solved by opening separte instances of Excel (Excel.exe
shortcut) and then opening the target file through the File > Open command.
The problem with this is that typical Excel users don't open files this way.

It seems to be a "feature" in Excel. A "feature" that I am too happy about
but there appears that nothing can be done.

Thanks everybody for their help.

Ed

Edward,
If this is a problem, you can set Application.IgnoreRemoteRequests=True, so
any files opened from Explorer will open in their own instance of Excel.
However, you then have a problem of communicating between the 2 instances.

However, it still seems that it would be better to use the various
Activate/Deactivate events to solve your problem.

NickHK

"Edward" <Edward@discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:7F50CE52-F8AE-44B0-A352-D65BDE4B9DD3@microsoft.com...
> Well, it appears that when you open a Workbook in Excel for the first time
> it
> opens it in an instance of Excel. Subsequent Workbooks that are opened,
> are
> opened in the same instance of Excel. This causes problems when running
> macros since there is only one thread of execution.
>
> This can be solved by opening separte instances of Excel (Excel.exe
> shortcut) and then opening the target file through the File > Open
> command.
> The problem with this is that typical Excel users don't open files this
> way.
>
> It seems to be a "feature" in Excel. A "feature" that I am too happy about
> but there appears that nothing can be done.
>
> Thanks everybody for their help.
>
> Ed
>

I think I found a solution to the issue of opening Workbooks in their own
instance of Excel.

1 - Select Folder Options (from Windows Explorer or My Computer)
2 - Select File Types tab and select XLS entry
3 - Click on Advance button
4 - Select Open and click on Edit
5 - Add "%1" to end of string

Ed