Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel:  Error message "Not Enough system resources to display"

Posted on 2011-02-10
16
735 Views
Last Modified: 2012-05-11
Experts!

I have read a few post on what "Not Enough system resources to display" is caused by.  I am still not clear on how to resolve this issue.  The file I am working on has not only been giving me issues but other people as well.

So the question I would like to ask, is how can I stop this from occuring?  I am making a dashboard for work which will be viewed by the owners of my company.  It is really important that this spreadsheet is functional.

Thank you all
 NOC-Reports-r10.xlsm
0
Comment
Question by:Maliki Hassani
  • 8
  • 8
16 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34867747
Ok - since you mentioned it on the last question, I've been working on it.  Perhaps we got lucky - sometimes it takes a while to work this type of problem out. Sometimes, it seems simple (just got lucky :)

Ok - I found this and it seems to relate to your problem and I can't seem to get the error message on system memory anymore

http://support.microsoft.com/kb/183503

I noticed the one case where your zoom on the summary tab is different from the sheets that it references.

So I changed the zoom on the refernece sheet - Summary Data to 83% the same as summary data
and the error went away!

So, quick brute-force method to always stay in sync (cautionary note at end):

When the summary sheet is activated, I set a pointer to current sheet, then get the zoom on the current sheet

then I go through every sheet and set the zoom to the same as the summary sheet (except for the summary sheet)

I revert back to the summary sheet when done

Cautionary note:  to do this routine, I turn application.EnableEvents to FALSE

This prevents any controls from working (because you'd get in an infinite loop, activating each sheet and changing its zoom)

at the end, I turn application.EnablEvents back to TRUE

SO - if for some reason your control stop working, run this macro:

TurnEventsBackOn() embedded in your module3

Hope this makes your "Not enough system resources to display completely"  problems for good!

I put this (below) in your Surveillance_Dashboard codepage


Cheers,

Dave
Private Sub Worksheet_Activate()
Dim mySheet As Worksheet
Dim currentZoom As Integer, currentSheet As Worksheet

Application.ScreenUpdating = False

    Set currentSheet = ActiveSheet
    
    Application.EnableEvents = False
    Range("A1:AD56").Select
    ActiveWindow.Zoom = True
    currentZoom = ActiveWindow.Zoom
    For Each mySheet In Application.Worksheets
        If mySheet.Name = ActiveSheet.Name Then
        'do nothing
        Else
            mySheet.Activate
            ActiveWindow.Zoom = currentZoom
        End If
    Next mySheet
    
    currentSheet.Activate
    
    Range("D2:L3").Select
    
    GoTo endwkshtsub
errSub:
    MsgBox "got an error - need to figure it out", vbOKOnly
endwkshtsub:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Open in new window

NOC-Reports-r10.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34867756
>>you'd get in an infinite loop...

If some sheets have activate code behind them, you might set off the las vegas lights!  Just to be safe, turn events off when you have a chance you could be invoking the same event, then turn them back on. (generally speaking)...

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34867793
I am trying to see if I can break it!!  So far so good!  By the way, do you know why all the listbox changes to a smaller size?  Very annoying! lol
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 42

Expert Comment

by:dlmille
ID: 34867796
it could because of your zooming.  comment all your zooming out and then close/open/etc., with it to see if that's what's doing it.  If it is, you could have some code resize the control after the zoom.

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34867806
You are are brilliant!  You are the man with those if then statements!! lol
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34867816
Don't know about that...

Anyway, it was brute force - you only have to zoom the reference sheets, not the whole workbook.  I did it quick and if you happen to reference a new one in the future the current code would cover, otherwise you'd have to remember...

I'm going to take a break and pay attention to my family tonite (at least till they go to bed, lol)!  So If I don't respond,  I'll catch back if you have an issue, later.

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34867821
Oh - on that code - needs an on error resume next statement at the beginning, and an on error goto 0 statement before the range select:

I'm not sure error checking is really needed - either it breaks and you see it break, or it breaks and you get a popup... :)


Private Sub Worksheet_Activate()
Dim mySheet As Worksheet
Dim currentZoom As Integer, currentSheet As Worksheet

Application.ScreenUpdating = False
	on error goto errsub
    Set currentSheet = ActiveSheet
    
    Application.EnableEvents = False
    Range("A1:AD56").Select
    ActiveWindow.Zoom = True
    currentZoom = ActiveWindow.Zoom
    For Each mySheet In Application.Worksheets
        If mySheet.Name = ActiveSheet.Name Then
        'do nothing
        Else
            mySheet.Activate
            ActiveWindow.Zoom = currentZoom
        End If
    Next mySheet
    on error goto 0
    currentSheet.Activate
    
    Range("D2:L3").Select
    
    GoTo endwkshtsub
errSub:
    MsgBox "got an error - need to figure it out", vbOKOnly
endwkshtsub:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:Maliki Hassani
ID: 34867823
Great!  Take care of home my friend..  I think it is time for me to head home from being at work for 13 hrs...  By the way I checked and it is the zoom that is causing it to change.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34867826
So everything's working ok now?

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34867827
gotcha
0
 

Author Comment

by:Maliki Hassani
ID: 34867829
No issues with errors so I will close out
Thanks
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34867830
I'll think about how to resize the listbox - get values before the zoom and set after???

of course, if you never changed your zoom you'd never have the problem, lol...

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34867835
Okay!  I will continue with the smaller graphs and get them all dynamic..  Yeah!!
0
 

Author Closing Comment

by:Maliki Hassani
ID: 34867839
The best!!
0
 

Author Comment

by:Maliki Hassani
ID: 34868326
Dave:

The list box are still auto sizing even without the vba code. hmm  I looked to see if there was a comman in the properties but nothing found.  Latest spreadsheet attached NOC-Reports-r10.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34868458
Ask your question.

:)

Dave
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question