excel 中vb组合框_Excel 2013中的工作表组合框问题

excel 中vb组合框

On of my favourite Excel tricks is to show a combo box, when you click a cell that has a drop down list. The combo box can have larger font, and it autocompletes, so it’s easier for people to use.

我最喜欢的Excel技巧之一就是显示一个组合框 ，当您单击具有下拉列表的单元格时。 组合框可以具有较大的字体，并且可以自动完成，因此人们更容易使用。

There is only one ActiveX combo box on the worksheet, and it is hidden until you click on a drop down cell. When it appears, it shows the data validation list for the selected cell.

在工作表上只有一个ActiveX组合框，并且在单击下拉单元格之前它一直处于隐藏状态。 出现时，它将显示所选单元格的数据验证列表。

组合框代码 (Combo Box Code)

There is code on the worksheet, that automatically runs when you select a cell. It checks to see if the cell has a validation list. If it does, then the code gets the name of the list that is used.

工作表上有代码，当您选择一个单元格时，该代码会自动运行。 它检查该单元格是否具有验证列表。 如果是这样，那么代码将获取所使用列表的名称。

That name is added to the combo box’s ListFillRange property, so it shows the correct list for the selected cell.

该名称将添加到组合框的ListFillRange属性中，因此它将显示所选单元格的正确列表。

Excel 2013问题 (Excel 2013 Problem)

Recently, I discovered a problem with these combo boxes in Excel 2013. A client had upgraded from Excel 2010 to 2013, and the combo boxes didn’t show the lists in the new version.

最近，我在Excel 2013中发现了这些组合框的问题。客户端已从Excel 2010升级到2013，并且组合框未显示新版本中的列表。

The code wasn’t entering the list name in the combo box’s ListFillRange property. When I tried to change the property manually, I couldn’t do that either. Excel just cleared out the property, after I filled it in.

该代码未在组合框的ListFillRange属性中输入列表名称。 当我尝试手动更改属性时，我也无法执行此操作。 在我填写完属性后，Excel才清除了该属性。

创建一个新名字 (Create a New Name)

The lists were named ranges based on Excel tables. One was named ProdList, and here is its definition:

列表被命名为基于Excel表的范围。 一个名为ProdList ，其定义如下：

   =tblProducts[Product]

= tblProducts [产品]

I thought the table might be causing the problem, so I created a new name, based on the first name:

我以为该表可能是导致此问题的原因，所以我根据名字创建了一个新名称：

1. On the Ribbon’s Formulas tab, click Define Name
   在功能区的“公式”选项卡上，单击“定义名称”

2. In the New Name window, type a one-word name -- ProdListB

   在“新名称”窗口中，键入一个单词的名称-ProdListB

3. Click in the Refers to box, and press the F3 key
   单击“引用”框中，然后按F3键

4. In the Paste Name window, click on the table-based name -- ProdList -- and click OK

   在“粘贴名称”窗口中，单击基于表的名称-ProdList-并单击“确定”。

5. Click OK to complete the name
   单击确定完成名称

When I tested the new name in the combo box, it worked fine. Excel accepted the name without any problems.

当我在组合框中测试新名称时，它工作正常。 Excel接受该名称没有任何问题。

不要使用基于表的名称 (Don’t Use Table Based Names)

So, if you are having a problem with the ListFillRange property in Excel 2013 worksheet combo boxes, it might be caused by table-based names. Try creating another name, based on the first one, and see if that solves the problem.

因此，如果您在Excel 2013工作表组合框中的ListFillRange属性遇到问题，则可能是基于表的名称引起的。 尝试根据第一个名字创建另一个名称，看看是否可以解决问题。

You can download a sample file with a worksheet combo box, to see the names that I created, as a workaround for this problem

您可以下载一个带有工作表组合框的示例文件 ，以查看我创建的名称，以解决此问题。

NOTE: This also affects the Forms control combo boxes in Excel 2013. Both types of combo boxes work with table based names in Excel 2010, and UserForm combo boxes in both versions work with table based names.

注意：这也会影响Excel 2013中的“表单”控件组合框。两种类型的组合框都可以在Excel 2010中使用基于表的名称，并且两种版本中的UserForm组合框都可以使用基于表的名称。

记住缩放设置 (Remember the Zoom Settings)

And while we’re talking about combo box problems, remember to keep the list sheet at 100% zoom, to prevent crashes when you click on a combo box on the data entry sheet.

而且，当我们谈论组合框问题时，请记住将列表表保持在100％缩放 ，以防止在数据输入表上的组合框上单击时发生崩溃。

In the survey that I have in that blog post, 26 people said “Yes”, a combo box had crashed on them, and only 2 said “No.”

在我在该博客文章中进行的调查中，有26个人说“是”，一个组合框撞在了他们身上，只有2个人说“否”。

In the next version of Excel, we might have to stand on one leg, and wear a pointy hat, if we want combo boxes to work! But I’ll probably keep using them.

在下一个版本的Excel中，如果希望组合框可以工作，我们可能必须站起来，戴上尖尖的帽子！ 但是我可能会继续使用它们。

翻译自: https://contexturesblog.com/archives/2014/09/18/worksheet-combo-box-problem-in-excel-2013/

excel 中vb组合框