[Dave Unger]

Hi Robert,

I started having similar problems quite recently (Run Time Error
75" ("Could Not Find The Specified Object"), seems to happen
sporadically when vba is trying to reference a form - doesn't seem to
limited to one particular form. I have a vague (very) notion that
there is an "orphaned" form object, caused by halting the code during
development. Also, whenever this happens, Excel will crash if I try
to save the application. This has been happening with increasing
frequency, and is quite frustrating. I'm running Winsows XP SP3 and
XL2007.

I'm just wondering if you've found the cause/solution to this yet.

regards,

Dave



On Dec 28 2009, 11:33*pm, "Robert Crandal" wrote:
This is follow-up related to my posting here on 12/19/09
regarding "Run Time Error 75" ("Could Not Find The
Specified Object".

Basically, my Userform1 is "disappearing" from my workbook
for some unknown reason, even if my workbook is untouched
for any period of time longer than 2 minutes. *If I come back to
my computer after 2 minutes, I will try to load myformwith this
code: * (which causes RT-Error 75)

* * * * * * * * *UserForm1.Show vbModeless *

I also get an Error 75 message if I try to run the following code:

* * * * * MsgBox "Name ofform: " & UserForm1.Name

Then, if I try to view my UserForm1 object in my VBAProject
view window, I get an error that says "Path/File sccess Error".

Does anybody have any theories why I cannot suddenly use
Userform1 in my workbook, especially if my code is not very
complicated at all?? * All I do is handle UserForm1_Initialize()
and Worksheet_Change() and all my code does is transfer
data from userform to spreadsheet and vice versa with very
very very basic VBA code. *I even set "Application.EnableEvents"
to false and true inside my Worksheet_change() function.

Could this be just an operating system issues?? *(I noticed
that this problem only occurs while using Excel 2007
(Trial Version) on Windows XP Professional SP2. *I never get
this error on Windows Vista using Excel 2007)

Might this problem just be related to a bad or bugged version
of Windows XP, Service Pack 2??? * Ican'tthink of anything else.

thank you!

[Robert Crandal]

Hello Dave!

You will be pleased to know that I HAVE discovered my own
solution to this annoying problem and here it is:

Only "load" your userform just ONCE and NEVER "unload" it!
If someone tries to close your form, rather than unloading the
form, just hide it instead. If someone tries to display your
form again, simply unhide it!

My point is, only "load" the form once, so that your form's
"initialize" function only gets called once. From that point,
simply write code that will hide or unhide the form itself,
but dont "unload" the form data structure from memory.

Let me know if you need code examples for this. I have
tested this thoroughly and I never see the RTE 75 error
any more!

As to what causes this problem, I am not entirely sure.
Are you using modeless forms when it crashes??

Robert



"Dave Unger" wrote in message
...
Hi Robert,

I started having similar problems quite recently (Run Time Error
75" ("Could Not Find The Specified Object"), seems to happen
sporadically when vba is trying to reference a form - doesn't seem to
limited to one particular form. I have a vague (very) notion that
there is an "orphaned" form object, caused by halting the code during
development. Also, whenever this happens, Excel will crash if I try
to save the application. This has been happening with increasing
frequency, and is quite frustrating. I'm running Winsows XP SP3 and
XL2007.

I'm just wondering if you've found the cause/solution to this yet.

regards,

Dave

[Dave Unger]

Hi Robert,

Thanks for the reply.

Only "load" your userform just ONCE and NEVER "unload" it!
If someone tries to close your form, rather than unloading the
form, just hide it instead. If someone tries to display your
form again, simply unhide it!

My usual practise is to display the form, retrieve the form data, then
unload the form - the procedure then carries on with the retrieved
data. In my case, the "x" box is locked out from the user, and
closing, opening and pretty much all else is under vba control. The
application I'm working on is very large, and has a large number of
forms (approx 30). I'll review all these to make sure that there
isn't a "slip up" somewhere.

As to what causes this problem, I am not entirely sure.
Are you using modeless forms when it crashes??

Interesting - as a matter of fact, the last few days I have been using
a modeless form (as a progress indicator), and that's when I have
noticed an increase in the failures. I'll certainly peruse that quite
carefully. Also, I noticed that once I get the "Run Time Error 75"
message, and I click OK, and then do nothing, after a few minutes
Excel will display the dreaded "Excel has to close .." error message.

Thanks for your insight on this, Robert, I'll let you know how this
works out for me.

regards,

Dave

[Robert Crandal]

My usual practise is to display the form, retrieve the form data, then
unload the form - the procedure then carries on with the retrieved
data. In my case, the "x" box is locked out from the user, and
closing, opening and pretty much all else is under vba control. The
application I'm working on is very large, and has a large number of
forms (approx 30). I'll review all these to make sure that there
isn't a "slip up" somewhere.


I did my tests on a new and empty project that only contains one
userform. I also put one pushbutton on Sheet1 which loads
the form as modeless. I then tried opening and closing the form a couple
times, then I noticed that Run Time Error 75 kept occurrring....

So, now I'm positive that this error is not due to an error in my code
at all, but rather an Excel or operating system specific error!

Just be careful if you have a modeless form that can be loaded and unloaded
constantly. I'm guessing that the form gets lost in memory somewhere once
the unload happens....hence my solution to NEVER unload a form and
resort to hide and unhide tactics.

Please do keep me informed if you discover anything new regarding this
matter. Thank you Dave!

Robert C.

[Dave Unger]

Hi Robert,

I did my tests on a new and empty project that only contains one
userform. *I also put one pushbutton on Sheet1 which loads
the form as modeless. *I then tried opening and closing the form a couple
times, then I noticed that Run Time Error 75 kept occurrring....

Would you mind showing me exactly how you're doing this? I set this
up, and try as I might, could not get it to fail!

Please do keep me informed if you discover anything new regarding this
matter. *Thank you Dave!

I certainly will.

regards,

Dave

[Robert Crandal]

Sure, I will look for my basic example that creates the error
and I will post it here ASAP.

However, I know for sure that if I load my modeless userform
just ONCE and never unload it, that I never see the Run Time
Error 75 any more. I still haven't pinpointed the exact problem,
but I now it happens randomly whenver I unload my userform
and try to load it again.

Are you still seeing the error??



"Dave Unger" wrote in message
...
Hi Robert,

I did my tests on a new and empty project that only contains one
userform. I also put one pushbutton on Sheet1 which loads
the form as modeless. I then tried opening and closing the form a couple
times, then I noticed that Run Time Error 75 kept occurrring....

Would you mind showing me exactly how you're doing this? I set this
up, and try as I might, could not get it to fail!