Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Supressing Excel Errors (2002 (10.1302.1219) SP-2)

    I am recieving the following error after modifying some code in my workbook: "Not enough system resources to display completely."

    I get this after adding a sub to automatically update my code from a network source, since many copies of the utility are out there... :

    <font color=blue>Sub</font color=blue> UpDateMainUserForm()
    <font color=blue>Dim</font color=blue> i <font color=blue>As Long</font color=blue>
    Application.EnableEvents = <font color=blue>False</font color=blue>
    range = ActiveWorkbook.Name
    Workbooks(range).Activate
    <font color=blue>With</font color=blue> ActiveWorkbook.VBProject.VBComponents _
    ("frmUserForm").CodeModule <font color=448800>'frmUserForm can be changed to the name of your userform...</font color=448800>
    <font color=blue>If</font color=blue> .CountOfLines > 0 <font color=blue>Then</font color=blue>
    <font color=blue>For</font color=blue> i = .CountOfLines <font color=blue>To</font color=blue> 1 <font color=blue>Step</font color=blue> -1
    .DeleteLines i
    <font color=blue>Next</font color=blue>
    <font color=blue>End If</font color=blue>
    .AddFromFile ("//server/directory/textfilecontainingcode.txt") <font color=448800>'change the path to the source of your text file</font color=448800>
    <font color=blue>End With</font color=blue>
    Application.EnableEvents = <font color=blue>True</font color=blue>
    <font color=blue>End Sub</font color=blue>

    Any ideas? I am spawning this code at startup, but it's giving me this error message. I'd like to either prevent the error from occuring, or prevent the error from displaying, as it seems to have no adverse effect on the workbook as a whole.

    Thanks Millions!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Supressing Excel Errors (2002 (10.1302.1219) SP-2)

    I get an error at:
    <pre>range = </pre>

    "Compile error, argument not optional" since you are using a property (range) as a variable name (a bad idea) without DIMing it first (another bad idea)

    You can eliminate the lines:
    <pre>range = ActiveWorkbook.Name
    Workbooks(range).Activate</pre>


    as unneccessary. The activeworkbook is alreay active so why activate it. Besides you are explicitly telling excel in the with construction to use the activework.

    I ran the code with the lines gone (and a different path) and it deletes the code the userform and adds the code from the txt file.

    I found no error as you describe using XL97. When does the error occur?
    Steve

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Supressing Excel Errors (2002 (10.1302.1219) SP-2)

    The code is replaced for me no problem, but immediately after replacing the code this error comes up. It seems supliferous, it doesn't really have any adverse impact on the program at all.

    I'm just looking for a generic way to disable the application errors, but I can't seem to find a command for it.

    Thanks for the help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Supressing Excel Errors (2002 (10.1302.1219) SP-2)

    I'm out of my depth here, but the error you get seems to me to be essentially different from your "normal" syntax or runtime error. I have my doubts about intercepting it in VBA...

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Supressing Excel Errors (2002 (10.1302.1219) SP-2)

    In stead of deleting just the codelines and adding the new ones from a file, try deleting the entire userform and import the updated one.

    To make this work however, you need to have VBA create a coffeebreak <img src=/S/smile.gif border=0 alt=smile width=15 height=15> between the deleting and the importing. Otherwise VBA will complain you are trying to import a form that already exists. Like this:

    Sub DelForm
    'Code to delete the form
    Application.OnTime Now, "ImportForm"
    End Sub

    Sub ImportForm()
    'Code to import form
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Supressing Excel Errors (2002 (10.1302.1219) SP-2)

    The problem might not be with the code but the spreadsheet itself. You might investigate the error message (completely in quotes) into google and search for it. There are several "worksheet things" which lead to this error that might be the problem. The code running might just be triggering excel to "see the problem" again, but a=not actually the problem.

    I recall the zoom level on different sheets can cause this error, but don't recall all the details.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •