Solved

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

Posted on 2011-02-10
16
728 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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
>>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
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
You are are brilliant!  You are the man with those if then statements!! lol
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
So everything's working ok now?

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
gotcha
0
 

Author Comment

by:Maliki Hassani
Comment Utility
No issues with errors so I will close out
Thanks
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
Okay!  I will continue with the smaller graphs and get them all dynamic..  Yeah!!
0
 

Author Closing Comment

by:Maliki Hassani
Comment Utility
The best!!
0
 

Author Comment

by:Maliki Hassani
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
Ask your question.

:)

Dave
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now