Userform problem with "Run Time Error 75"


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

New Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average. Display Modes
  #1  
Unread 29th December 2009, 06:40
Robert Crandal's Avatar
Robert Crandal
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Userform problem with "Run Time Error 75"
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

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 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!



The Code Cage Advertisment
Advertisement
  #2  
Unread 29th December 2009, 06:55
joel's Avatar
joel joel is offline
Expert Member
MS Office Version: MS Office 97
MS Office Skill Level: New to MS Office
 
Join Date: Apr 2009
Age: 58
Posts: 1,252
Posts Per Day: 0.60
My Top Tip Count: 0
Thanks to others: 0
Thanked 6 Times in 6 Posts
Reputation: 220 (20) joel has a spectacular aura aboutjoel has a spectacular aura aboutjoel has a spectacular aura about
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


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.

  #3  
Unread 29th December 2009, 07:59
Robert Crandal's Avatar
Robert Crandal
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


Both Vista and XP were set to "Break on Unhandled Errors". I tried
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.43xyv8@thecodecage.com> wrote in message
news:joel.43xyv8@thecodecage.com...
>
> 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.
>



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #4  
Unread 29th December 2009, 12:46
joel's Avatar
joel joel is offline
Expert Member
MS Office Version: MS Office 97
MS Office Skill Level: New to MS Office
 
Join Date: Apr 2009
Age: 58
Posts: 1,252
Posts Per Day: 0.60
My Top Tip Count: 0
Thanks to others: 0
Thanked 6 Times in 6 Posts
Reputation: 220 (20) joel has a spectacular aura aboutjoel has a spectacular aura aboutjoel has a spectacular aura about
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


Can you provide the function "MyUserformIsLoaded" and let me know where the code is located?

  #5  
Unread 29th December 2009, 19:08
Robert Crandal's Avatar
Robert Crandal
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


Sure.... I'm using code that was provided by "Peter T" from this
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.43yf2z@thecodecage.com> wrote in message
news:joel.43yf2z@thecodecage.com...
>
> Can you provide the function "MyUserformIsLoaded" and let me know where
> the code is located?
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:
> http://www.thecodecage.com/forumz/sh...d.php?t=165579
>
> Microsoft Office Help
>



  #6  
Unread 29th December 2009, 23:09
joel's Avatar
joel joel is offline
Expert Member
MS Office Version: MS Office 97
MS Office Skill Level: New to MS Office
 
Join Date: Apr 2009
Age: 58
Posts: 1,252
Posts Per Day: 0.60
My Top Tip Count: 0
Thanks to others: 0
Thanked 6 Times in 6 Posts
Reputation: 220 (20) joel has a spectacular aura aboutjoel has a spectacular aura aboutjoel has a spectacular aura about
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


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

The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #7  
Unread 30th December 2009, 09:43
Robert Crandal's Avatar
Robert Crandal
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


Hi Joel. Thanks for catching that minor function flaw.
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.43z7zq@thecodecage.com> wrote in message
news:joel.43z7zq@thecodecage.com...
>
> 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.
>



  #8  
Unread 30th December 2009, 12:12
joel's Avatar
joel joel is offline
Expert Member
MS Office Version: MS Office 97
MS Office Skill Level: New to MS Office
 
Join Date: Apr 2009
Age: 58
Posts: 1,252
Posts Per Day: 0.60
My Top Tip Count: 0
Thanks to others: 0
Thanked 6 Times in 6 Posts
Reputation: 220 (20) joel has a spectacular aura aboutjoel has a spectacular aura aboutjoel has a spectacular aura about
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


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.

  #9  
Unread 6th January 2010, 06:26
Dave Unger's Avatar
Dave Unger
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


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" <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!



The Code Cage Advertisment
Advertisement
  #10  
Unread 7th January 2010, 11:28
Robert Crandal's Avatar
Robert Crandal
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Userform problem with "Run Time Error 75"

------ Register to get rid of these "In Post" ads! ------


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" <dave.unger@sasktel.net> wrote in message
news:aabe2430-d8d2-4089-aea3-d830fc8e8e56@g18g2000vbr.googlegroups.com...
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




The Code Cage Advertisment
Advertisement
New Reply
The Code Cage at Facebookhe Code Cage at Twitter


Tags
problem, run time error 75, userform

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 23:15.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University