Userform problem with "Run Time Error 75"
Robert Crandal
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 my form with 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 of form: " & 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??? I can't think of anything else.
thank you!
joel
check the Error trapping setting with Vista and XP in the VBA menu
Tools - Options - General - Error Trapping.
I usually operate with Stop On All Errors because I usually find
skipping one error in the code usually creates another error futher in
the code. If I fix the first error the second error also gets fixed.
also temperarily comment out all ON ERROR statements. Sometimes you
error trapping isn't working properly and produces a 2nd error. It is
best to try to correct all errors and not skip through errors.
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165579
[url="http://www.thecodecage.com"]Microsoft Office Help[/url]
Robert Crandal
setting Windows XP SP2 to "Break On All Errors", but I didn't notice
anything different. My userform1 still "cannot be found".
My code really is very very simple and basic. It just has a button
on Sheet1 which calls "Userform1.Show vbModeless". It works fine
after 2 minutes or so, but if I come back to the computer and press
the button to call "Userform1.Show vbModeless", that is when I
get the above stated error messages/codes.
My Worksheet_Change() function for Sheet1 looks like this:
Private Sub Worksheet_Change (ByVal Target As Range)
If MyUserformIsLoaded("UserForm1") Then
Application.EnableEvents = False
Userform1.Caption = Sheet1.Range("A1").Value
Application.EnableEvents = True
End If
End Sub
Also, my workbook only crashes on Windows XP SP2. Since my
code is very basic and simple and it runs great on Windows Vista,
I'm inclined to believe that something is wrong with my Windows XP
machine, but I'm not positive. I just wanted to get everybody's feedback
here to see if anyone has run into this problem before.
"joel" <joel....@thecodecage.com> wrote in message
news:joel....@thecodecage.com...
joel
Can you provide the function "MyUserformIsLoaded" and let me know where
the code is located?
Robert Crandal
newsgroup. Here it is:
Function IsFormLoaded (sFrmName As String) As Boolean
Dim i As Long
Dim bIsLoaded As Boolean
For i = 1 To UserForms.Count
If UserForms(i - 1).Name = sFrmName Then
bIsLoaded = True
Exit For
End If
Next
IsFormLoaded = bIsLoaded
End Function
I simply renamed the function to "MyUserformIsLoaded" wherever
needed!
"joel" <joel....@thecodecage.com> wrote in message
news:joel....@thecodecage.com...
>
joel
I think I found the problem. I set bIsLoaded = False in the function
below. Without this statement the function was returing nothing which
may of caused problems with your code.
Function IsFormLoaded(sFrmName As String) As Boolean
Dim i As Long
Dim bIsLoaded As Boolean
bIsLoaded = False
For i = 1 To UserForms.Count
If UserForms(i - 1).Name = sFrmName Then
bIsLoaded = True
Exit For
End If
Next
IsFormLoaded = bIsLoaded
End Function
Robert Crandal
Unfortunately, my workbook is still crashing with a Run Time
Error 75 because my Userform1 object seems to keep
vanishing.
I have stripped down my code to only the following which
is placed in my Userform1 code module.
-------------------------------------------------------------------------------
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLongA Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Const GWL_HWNDPARENT As Long = -8
------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
Dim hWnd As Long
hWnd = FindWindow("ThunderDFrame", Me.Caption)
SetWindowLongA hWnd, GWL_HWNDPARENT, 0&
End Sub
-------------------------------------------------------------------------------
' Module 1
Sub Button1_Click()
Userform1.Show vbModeless
End Sub
------------------------------------------------------------------------------
The problem seems to be the "SetWindowLongA" function call which changes the
window style to GWL_PARENT.
I know that if I get rid of this call to SetWindowLongA that my Userform1
will
never disappear at random times, but I really love having this special popup
window
that remains visible even when Excel is minimized.
Also, the above code works fine on my Windows Vista machine. It only
crashes
on Windows XP, Service Pack 2. Try loading the userform a couple times,
walk away from your computer for about 2-5 minutes after the screen saver
turns
on, then try to load the userform again. That is when I get the message
"Could not find the specified object"
How about if I set the Userform flag back to it's original setting when the
user
closes out or unloads the form??? In other words, how can I change the
window flag from GWL_HWNDPARENT back to it's original setting???
I'm not sure how I would do that??
"joel" <joel....@thecodecage.com> wrote in message
news:joel....@thecodecage.com...
>
joel
I'm using XP SP3 (not 2) with Office 2003 and not having the problem.
My Monitor is set to 800 x 600.
I verified my SP3 by going to control panel and selecting Add/Remove
Programs. At the end of the list of installed software is Windows XP
Service Pack 3.
I also chaeck the version of windows by getting a DOS window (cms.exe).
the top of the DOS window show 5.1.2600. I upgrade a couple weeks ago
manually so the exact numbers may not match.
Dave Unger
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" <nob...@gmail.com> 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
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" <dave....@sasktel.net> wrote in message
news:aabe2430-d8d2-4089...@g18g2000vbr.googlegroups.com...
Dave Unger
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
> 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
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" <dave....@sasktel.net> wrote in message
news:805cc9c8-0a06-4bb9...@w12g2000vbj.googlegroups.com...
Dave Unger
> Sure, I will look for my basic example that creates the error
> and I will post it here ASAP.
Looking forward to it.
> 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??
Yes, it's still happening, sporadically, but I can't seem to generate
it at will. I've also something else when I get that error. Let's
say I have forms A, B, C, D, E in the vba object list. Assume that I
get the error while trying to access form C. Now I find I can open
any form above that (A to B in this case), but nothing from C on down
(C to D). I'm getting out of my depth here, but it seems that the
form object list has somehow been severed at that point.
I'm hoping your example will allow me to re-create this anytime.
regards,
Dave
Dave Unger
> it at will. I've also something else when I get that error. Let's
> say I have forms A, B, C, D, E in the vba object list. Assume that I
> get the error while trying to access form C. Now I find I can open
> any form above that (A to B in this case), but nothing from C on down
> (C to D).
Sorry, typo in my last message, above line shoud be:
Now I find I can open any form above that (A to B in this case), but
nothing from C on down
(C to E).
Robert Crandal
>
> I'm hoping your example will allow me to re-create this anytime.
>
Operating System: Window XP, Service Pack 3 (upgraded recently from SP 2)
Software used: Excel 2007
Steps to follow:
1) Create a new/blank-workbook project.
2) Insert new "userform" object into your project.
(Leave the form empty, with no controls!)
3) Insert a "pushbutton" (form control) on top of Sheet1.
4) Assign new macro for "Button1", which by default
links with the "Button1_Click()" subroutine.
5) In your "Button1_Click()" subroutine, enter the following
code: UserForm1.Show vbModeless
6) Go to Sheet1 and press "Button 1".... this will display your
userform in modeless form.
7) Close the userform by pressing the "X" button.
8) On Sheet1, type random data into any 3 or more cells. Go to
Sheet2 and Sheet3 and do the same. Just enter random data
into all sheets, then switch back to Sheet1.
9) Now leave your PC alone for 3 or more minutes. My screensaver
starts after 3 minutes.
10) Come back to your PC and try to load the userform again by
pressing "Button 1". It is at this point where you should see
any of the following errors: "Path/File access error" or
"Cannot find specified object". If you don't see an error message
right away, keep repeating steps 7 to 10 above until you see the
error message.
Many people here have suggested that possibly there was an error in my
code, but as you can see above I have created a completely empty project
that contains only ONE userform, ONE pushbutton, and ONE line of
code, yet I am still seeing an error message. This must be an Excel
or operating system specific bug, don't you think???
Anyways, the only workaround that I found was to avoid "unloading"
the modeless userform; if a user tries to close the form by pressing "X",
I simply hide the form instead of unloading it. (I then unhide or show it
again if necessary!)
Perhaps I am missing an upgrade or patch for Excel 2007??? If I finally
solve this mystery I will surely let you know Dave! Please run the above
steps and tell me if you see the same error. Also let me know if you
figure out how to fix this. Thank you!
Robert C.
Dave Unger
Thanks for this. I set up your scenario and sure enough, I get that
error message too.
Additionally, if I close this workbook before I get the message, then
open another application in the same instance of Excel, the new
application will also generate the error message. I haven't been able
to track anything down yet, sometimes I'll get the message after a
couple of minutes, other times it'll be Ok for 15 - 20 minutes.
I'll let you know if anything else develops.
regards,
Dave
PS - I am surprised that no one else has reported having this problem.
boggimedes
I have been having this issue on a few of my sheets. I only just
recently found the forum because previously I only noticed the crash
when I attempted to save. Once I realized it was attached to the
userform I changed my search string and came up with this.
I am having this same issue. As far as I have been able to test not
"unloading" doesn't resolve anything. It will still crash. I am
running windows 7. Has anyone found a solution to this issue?
--
boggimedes
------------------------------------------------------------------------
boggimedes's Profile: http://www.thecodecage.com/forumz/member.php?u=1798
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165579
Dave Unger
On Apr 25, 8:29 am, boggimedes <boggimedes.49z...@thecodecage.com>
wrote:
> I have been having this issue on a few of my sheets. I only just
> recently found the forum because previously I only noticed the crash
> when I attempted to save. Once I realized it was attached to the
> userform I changed my search string and came up with this.
>
> I am having this same issue. As far as I have been able to test not
> "unloading" doesn't resolve anything. It will still crash. I am
> running windows 7. Has anyone found a solution to this issue?
>
No, not yet (speaking for myself). However, it's still happening.
Sometimes it will be good until I try to save, then I get the error,
which is extremely frustrating. Anyway, from your message it seems
like the problem isn't limited to windows XP. I am a bit surprised we
haven't heard from more people on this.
regards,
DaveU
boggimedes
I am surprised as well, though I suppose it is probably much less common
that people use sheets where they open and close userforms. Plus I had
a really hard time figuring out it was a userform issue. I thought it
just crashed randomly at first. Then I realized it was any time I
saved (or it autosaved). Still was some time before I noticed the
pattern. Anyway, DO have a solution, though its not the best one.
Office 2010 beta is out, and it resolves the issue. Just proves once
and for all that it is nothing to do with how we coded. Must be
something in the way 2007 handles userforms that they changed in the new
version. Doubtful that MS will try to fix it in 07 now that 10is on the
horizon. Looks like we'll all just have to upgrade.... again.
Hey, at least its fixed somehow! :-) I'll keep myself on the
notification list for this forum just in case someone else smarter than
I finds a solution.
Dave Unger
Thanks for the heads up re Office 2010. You're probably right about
"updating" to fix a microsoft problem, but as you suggest, what choice
do we have?
regards,
DaveU
On Apr 27, 7:20 am, boggimedes <boggimedes.4a2...@thecodecage.com>
wrote: