excel 中vb组合框_单击组合框后Excel崩溃

最新推荐文章于 2024-09-14 18:46:05 发布

culiao2169

最新推荐文章于 2024-09-14 18:46:05 发布

阅读量364

点赞数

原文链接：https://contexturesblog.com/archives/2013/07/02/excel-crashing-after-combo-box-click/

版权

excel 中vb组合框

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.

上周，我正在更新我的网页，该网页显示了如何在具有下拉列表的单元格中显示组合框。这是该工作簿的屏幕截图。

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.

我在多个版本的Excel中都使用了此技术，直到现在都从未遇到过问题。

点击并崩溃 (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.

因为您可以在组合框中设置字体大小，所以如果将工作表缩放到100％以下，效果会更好。在较低的缩放设置下，可能很难读取数据验证列表中的那些小字母，因此较大的字体会有所帮助。

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.

为了显示字体大小的差异，我将工作表放大到70％，然后单击组合框中的箭头。 Excel 2013崩溃了。我尝试了几次，然后又一次崩溃了。

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.

我在Excel 2010中尝试了相同的操作，在该处出现了熟悉的旧消息–“系统资源不足，无法正确显示”。然后，Excel 2010崩溃了。

问题在2000年解决了吗？ (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.

经过长时间的Google搜索，我发现了一些论坛帖子，这些帖子链接到有关此问题的MSKB文章。显然，本文列出了导致错误的条件，并且有一种解决方法。

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

不幸的是，该文章已退休，因为该问题已在Excel 2000中解决。嗯，至少在我的机器上似乎又回来了。我在使用Excel 2013的Windows 8计算机上，因此远远超出了“解决”问题的版本。

导致问题的条件 (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.

幸运的是，您可以在WayBack Machine中找到各种各样的东西，这就是我找到丢失文章的档案的地方。您可以在本文末尾看到它。

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

哪些条件可以产生此错误消息，或者在我的情况下会导致Excel崩溃？

The Input range of the control is linked to a second worksheet.
控件的输入范围链接到第二个工作表。
You zoom both worksheets to percentages other than 100 percent.
您将两个工作表缩放到100％以外的其他百分比。
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
两张纸都放大80％
I changed the combo box sheet to 70%, to show the difference in font size
我将组合框工作表更改为70％，以显示字体大小的差异

解决方法 (The Workaround)

Here are three workarounds for the problem:

这是此问题的三种解决方法：

Change the zoom setting of either worksheet to 100 percent.
将任一工作表的缩放设置更改为100％。
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.

我选择了选项1，并将具有指定范围的图纸的缩放级别设置为100％。我将在该工作表上添加一条大消息，警告人们不要放大。

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!

有了该缩放设置，就不再发生崩溃。但是，当我将其更改为90％并测试组合框时，它再次崩溃了。不要那样做！

组合框应如何工作 (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

在此视频中，您可以看到组合框没有崩溃时的工作方式。您可以在我的Contextures网站上下载示例文件，并获取设置说明：使用命名范围的数据验证组合框

演示地址

缺少的MSKB文章 (The Missing MSKB Article)

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

这是我在Internet存档中找到的MSKB退休文章：________________

缩放工作表上的控件“系统资源不足” ("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

文章ID：183503退休的KB内容免责声明如果您在工作表中多次单击以下各项，或者在具有以下任何一项的工作表上移动了垂直工具栏，则可能会出现以下问题：

list box
列表框
drop-down list box (Microsoft Excel versions 5.0 or 7.0 only)
下拉列表框(仅适用于Microsoft Excel 5.0或7.0版)
combo box (Microsoft Excel 97 only)
组合框(仅Microsoft Excel 97)

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.
您将两个工作表缩放到100％以外的其他百分比。

-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.
将任一工作表的缩放设置更改为100％。单击视图菜单上的缩放，单击100％，然后单击确定。

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

更改控件的输入范围若要更改控件的输入范围，请按照下列步骤操作：

Hold down the CONTROL key and click the form control to select the control.
按住CONTROL键，然后单击窗体控件以选择该控件。
On the Format menu, click Control.
在格式菜单上，单击控件。
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.

回到顶端状态Microsoft已经确认这是本文开头列出的Microsoft产品中的问题。在Microsoft Excel 2000中不再发生此问题。