• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

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

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
Maliki Hassani
Asked:
Maliki Hassani
  • 8
  • 8
1 Solution
 
dlmilleCommented:
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
 
dlmilleCommented:
>>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
 
Maliki HassaniAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
You are are brilliant!  You are the man with those if then statements!! lol
0
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
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
 
dlmilleCommented:
So everything's working ok now?

Dave
0
 
Maliki HassaniAuthor Commented:
gotcha
0
 
Maliki HassaniAuthor Commented:
No issues with errors so I will close out
Thanks
0
 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
Okay!  I will continue with the smaller graphs and get them all dynamic..  Yeah!!
0
 
Maliki HassaniAuthor Commented:
The best!!
0
 
Maliki HassaniAuthor Commented:
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
 
dlmilleCommented:
Ask your question.

:)

Dave
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now