Excel Crashing After Combo Box Click

Excel Crashing After Combo Box Click

Last week, I was updating my web page that shows how to show a combo box in a cell that has a drop down list. Here’s a screen shot of that workbook.

datavalcombosheet14

The combo box has several advantages over the default data validation list. You can:

  • change the font size,
  • increase the number of visible rows
  • start typing an entry, and it autocompletes, if a match is found

I’ve used this technique in several versions of Excel, and never had problems – until now.

Click and Crash

Because you can set the font size in a combo box, it should work better if the worksheet is zoomed below 100%. At lower zoom settings, it can be hard to read those tiny letters in the data validation list, so a larger font helps.

To show the difference in font sizes, I zoomed the worksheet to 70%, then clicked on the arrow in the combo box. Excel 2013 crashed. I tried it a few more times, and it crashed again and again.

datavalcombosheet16

I tried the same thing in Excel 2010, where that old familiar message appeared – “Not enough system resources to display correctly”. And then Excel 2010 crashed.

The Problem Was Fixed in 2000?

After a lengthy Google search, I found a couple of forum posts that linked to an MSKB article about this problem. Apparently this article listed the conditions that led to the error, and had a workaround.

Unfortunately, that article is retired, because the problem was solved in Excel 2000. Well, it seems to be back, on my machine, at least. I’m on a Windows 8 computer, using Excel 2013, so that’s well past the version in which the problem was “solved”.

The Conditions That Cause the Problem

Fortunately, you can find all kinds of things in the WayBack Machine, and that’s where I found an archive of the missing article. You can see it at the end of this article.

What conditions can create this error message, or in my case, an Excel crash?

  • The Input range of the control is linked to a second worksheet.
  • You zoom both worksheets to percentages other than 100 percent.
  • The zoom percentage of the two sheets are not equal.

And that’s how my workbook was set up:

  • My combo box gets its list from a named range on another worksheet
  • Both sheets were at 80% zoom
  • I changed the combo box sheet to 70%, to show the difference in font size

The Workaround

Here are three workarounds for the problem:

  • Change the zoom setting of either worksheet to 100 percent.
  • Change the zoom setting of both sheets to the same percentage.
  • Select an input range that is on the sheet with the list box, drop-down list box, or combo box.

I chose option 1, and set the zoom level for the sheet with the named ranges to 100%. I will add a big message on that sheet, warning people to leave the zoom alone.

With that zoom setting, there were no more crashes. But, when I changed it to 90%, and tested the combo box, it crashed again. Don’t do that!

How the Combo Box Should Work

In this video, you can see how the combo box works, when it isn’t crashing. You can download the sample file, and get the setup instructions, on my Contextures website: Data Validation Combo Box using Named Ranges

The Missing MSKB Article

Here is the retired MSKB article that I found in the Internet Archive:
________________

“Not Enough System Resources” with Controls on Zoomed Sheets

Article ID: 183503
Retired KB Content Disclaimer
This article was previously published under Q183503
SYMPTOMS
If you click the following items in a worksheet multiple times or if you move a vertical toolbar over a worksheet with any of the following items

  • list box
  • drop-down list box (Microsoft Excel versions 5.0 or 7.0 only)
  • combo box (Microsoft Excel 97 only)

the following message may appear:
Not enough system resources to display completely
CAUSE
This problem may occur when the following conditions are true.

  • The Input range of the control is linked to a second worksheet.
    -and-
  • You zoom both worksheets to percentages other than 100 percent.
    -and-
  • The zoom percentage of the two sheets are not equal.
    -and-
  • You move a toolbar over the control, click the control and choose values, or you alternately select each of the two worksheets.

NOTE: Sometimes an action does not result in the message, but repeated actions do. The message only occurs when the worksheet that contains the control is active.
WORKAROUND
To work around this problem, do any of the following:

  • Change the zoom setting of either worksheet to 100 percent. Click Zoom on the View menu, click 100% and click OK.
    -or-
  • Change the zoom setting of both sheets to the same percentage. Click Zoom on the View menu to make this change.
    -or-
  • Select an input range that is on the sheet with the list box, drop-down list box, or combo box.

Changing the Input Range of a Control
To change the input range of a control, follow these steps:

  1. Hold down the CONTROL key and click the form control to select the control.
  2. On the Format menu, click Control.
  3. Click the Control tab and type a range on the active worksheet.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.

________________________

0 thoughts on “Excel Crashing After Combo Box Click”

  1. Thanks for the reference to the “Wayback Machine”.
    I’ve added that to my Firefox bookmarks.
    Wondering if anybody has started a list of fixes for the “improvements” to XL2013 ?

    1. @Jim, you’re welcome, and I’m glad that you like Wayback Machine. It’s a great resource, and a good reminder that the Internet never forgets. 😉
      If you ever find that fix list, please let me know!

  2. Thanks for this blogpost! I came across this error for the first time tonight and when I adjusted the zoom, the problem was solved.

  3. Thanks so much for the tip with the zoom. This error drove me mad for ages and now its solved! 🙂

  4. By the way: I’m working with Excel 2013. So the problem hasn’t been solved, at least not for the 2013 version.

  5. I have the same issue with Excel 2010 files running on Win7, but the zoom fix doesn’t prevent crashes in my cases. The same exact network accessible file will operate normally on some computers but crash on others when a combo-box drop-down list is clicked. Every couple months another computer gets added to the “crash” column. This has been going on for a couple years with no end in site! Unfortunately upgrading Office or Windows is not an option.

  6. Using Windows 7 and Using Excel 2016. Cicking on a drop down just started crashing for me. Validation list is on a separate worksheet but both zoom levels are at 100%.

  7. This “old feature” just came back in Excel 2016 version 1611 Build 7571.2109 (Current Channel).
    Happens when clicking in a drop down Form control inserted over a chart sheet. Of course the Input Range and linked Cell are in another Sheet.
    No matter the zoom is (even 100% on both sheets)
    Tested it on several machines and sistematically reproduced the crash on all having that Office version, with win7 or win10.
    Some other Office versions we have beeing using are not subject to this error, and do work perfectly (1605 Build 6965.2115 from deferred channel for. ex).
    Tryed changing default printed. No success.
    No addins active.
    Let’s hope next release will remove the problem.
    Has anyone experienced this again?

  8. Yes indeed the old feature is back in Excel 2016 version 16.0.4266.1001. Even though the sheets are at 100% zoom it still crashes. But after opening the file move the slider forward and then back to 100%, the combo works. I hope they fix this soon.

Leave a Reply to arun Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.