Time-Tracker form, multiple instance of Excel

[Duncan]

Hi all,

Firstly can I just agree with Pianoman that this is definately the best
forum I have come across and always get an answer. Very satisfied with
the level of compassion and helpfulness here!

Anyway, I posted the other day about this timetracker im trying to
create, now i have nearly finished it but I want to iron out some
problems with opening instances of excel.

As the form opens up vbmodless I minimise the application behind it so
that it looks standalone, I also have borrowed a lot of code from
formfun.

But what would be the best approach if people want to use excel for
other things at the same time? I know I can get things to open in their
own instance by opening excel first and then file open the file I want,
but if people click on shortcuts to excel docs then it just shows the
form and does nothing, likewise in many other situations.

Is there a better way of making this thing fairly standalone as I want
it to always be on screen while the users are doing other things, or
should I change the way users open their other excel docs?

Any help would be great on this as im not really clued up on modeless
apps and only really want the form to stay open until closed (ive
allowed the minimise button so it dont get in the way) and I would love
it to be not closed except by my close button.

Any ideas anyone?

Many thanks in advance

Duncan

 

[Tom Ogilvy]

for the instance of excel using your form, set the IgnoreRemoteRequests
property of the Application Object to false and perhaps that should allow
users to get a different instance of excel. This is equivalent to going into
tools=>Options and in the general tab, selecting Ignore Other applications.
See if that gives you the functionality you want.

the queryclose event of the form should allow you to control closing of your
userform.

 

[Duncan]

Tom,

I put this in the workbook open bit


With Application
.IgnoreRemoteRequests = False
End With

But if I open another excel that has macro or any vba code then it
opens up but stays on the start bar and i cannot bring it up in view
(very confusing)

This might have something to do with the fact that I have a timer clock
thing on my form that is updating every second, this might be stopping
the other vba from working and or not allowing the other workbook to
properly open, i dont know. I do know that my clock stops ticking on
the form after I open something else and that is why i am thinking down
these lines, I might actually remove the clock but I would love to be
able to keep it.

As for the workbook close I have tried all sorts of select case and
message boxes with an if stating cancel = true but it still closes?!
any ideas?

This actual workbook is really bugging me full stop, its not behaving
like any of my others and I have to put an application.quit after my
workbook close line behind my close button or the workbook closes and
then re-opens?!

I am sure that all of this ties down to my timer thing constantly
running every second, but I cant be sure, I do have running = false
lines behind the close button in case it was still trying to run the
vba and that was why it was re-opening but when that didnt work I
resorted to application.quit.

Very confused am I

Duncan

 

[Tom Ogilvy]

queryclose controls the closing of the userform. If the application is
hidden, I am not sure how the user could close it.

If the other workbook is opening in a new instance of excel, it shouldn't be
affected by the first instance of excel except that both are trying to use
the processor - but this would be true for any other application. Perhaps
it is opening in the same instance, but I would expect ignoreremoterequests
to prevent that.

If your workbook is opening back up, then that would be indicative that your
instance of excel is still open. You should close it when your userform
drops and you close your workbook.

 

[Duncan]

when you say "You should close it when your userform drops and you
close your workbook." do you mean application.quit? or can you close an
instance of excel via other means?

Duncan

 

[Tom Ogilvy]

Yes, I do mean
Application.Quit

but you can't close the workbook separately - because as soon as your
workbook closes, the code stops executing (and you say your application.Quit
is after that command). the workbook should be closed as a by product of
using application.Quit.