?
Solved

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

Posted on 2011-02-10
16
Medium Priority
?
743 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
[X]
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
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

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

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!

Question has a verified solution.

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

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

766 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