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

Combo Box Crash Survey


________________________