vba 指向基于公式的命名范围的 Excel 组合框 listfillrange 属性存在问题

Excel combobox listfillrange property pointing at a formula-based named range has issues

提问by Kimball Robinson

ActiveX combobox objects in Excel do not behave well when their ListFillRange refers to a formula-based Named Range (Defined Name).

当它们的 ListFillRange 引用基于公式的命名范围（定义名称）时，Excel 中的 ActiveX 组合框对象表现不佳。

I think I have encountered other errors and possibly even Excel crashes thanks to this, but right now all that happens is the combobox_change() event is triggered anytime ANY cell in the workbook is changed.

我想我遇到了其他错误，甚至可能因此导致 Excel 崩溃，但现在发生的只是在工作簿中的任何单元格发生更改时都会触发 combobox_change() 事件。

I am not sure if this is really a bug, or if there is a fix, or a workaround. If it is a bug, how do I report it to the Excel people?

我不确定这是否真的是一个错误，或者是否有修复或解决方法。如果它是一个错误，我如何将其报告给 Excel 人员？

And finally, the real meat of my question is "How do I work around this issue best?" I would like to have some formula-based named ranges, but it seemslike this won't be possible.

最后，我问题的真正重点是“我如何最好地解决这个问题？” 我想要一些基于公式的命名范围，但这似乎是不可能的。

To reproduce this bug, do the following:

要重现此错误，请执行以下操作：

1. Create a new workbook. On Sheet3, create a small table 3 columns across, and several rows high.
2. Create a named range with this formula (or an equivalent): =OFFSET(Sheet3!$A$2:$C$36,0,0,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$4:$4)) To do this use Input>Name>Define. Name the range something like "demoRange"
3. Go to Sheet1 and create a combobox, (it must be on a separate sheet). (Use the Control Toolbox menu, notthe Forms menu).
4. Click on the Design Mode button (the blue triangle with pencil), then right click on the combo box and go to Properties.
5. In the properties window for the combobox, change the ListFillRange property so that it points at the named range you created in step 2 ("demoRange").
6. You may want to change the ColumnCount property to 3, and the ColumnWidths property to "50,50,50"
7. Set the linkedCell property to cell "A1" by typing A1 in the linkedCell property.
8. Close the properties window, and double click on the combobox to define its change() event.
9. Put a Debug.Assert(false) or Msgbox("demo") line in the subroutine for the new combobox's change event.
10. Exit design mode
11. important- Now select an item in the combobox. The event should trigger normally the first time. (The bug will not show if you don't do this step--something must be selected in the combobox)
12. Edit cells anywhere in the workbook [Edit] or any other open workbook [/edit], on any sheet and any location. Each time you edit any cell, (at least for me), the onchange event for the combo box is run.

1. 创建一个新的工作簿。在 Sheet3 上，创建一个小表，宽 3 列，高几行。
2. 使用以下公式（或等效公式）创建命名范围：=OFFSET(Sheet3!$A$2:$C$36,0,0,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$4:$4))为此，请使用输入>名称>定义。将范围命名为“demoRange”
3. 转到 Sheet1 并创建一个组合框，（它必须在单独的工作表上）。（使用控件工具箱菜单，而不是窗体菜单）。
4. 单击“设计模式”按钮（带铅笔的蓝色三角形），然后右键单击组合框并转到“属性”。
5. 在组合框的属性窗口中，更改 ListFillRange 属性，使其指向您在步骤 2 中创建的命名范围（“demoRange”）。
6. 您可能希望将 ColumnCount 属性更改为 3，将 ColumnWidths 属性更改为“50,50,50”
7. 通过在linkedCell 属性中键入A1，将linkedCell 属性设置为单元格“A1”。
8. 关闭属性窗口，双击组合框来定义它的 change() 事件。
9. 在新组合框的更改事件的子例程中放置 Debug.Assert(false) 或 Msgbox("demo") 行。
10. 退出设计模式
11. 重要- 现在在组合框中选择一个项目。事件应该在第一次正常触发。（如果您不执行此步骤，则不会显示该错误 - 必须在组合框中选择某些内容）
12. 在任何工作表和任何位置编辑工作簿 [Edit] 或任何其他打开的工作簿 [/edit] 中任意位置的单元格。每次编辑任何单元格时（至少对我而言），组合框的 onchange 事件都会运行。

Again, is this normal, and what is the best alternative for what I am doing? This combo box gets linked to various cells, and is supposed to be a replacement for the tiny font in the data validation dropdowns excel provides by default.

同样，这是否正常，我正在做的事情的最佳选择是什么？这个组合框链接到各种单元格，应该是默认情况下excel提供的数据验证下拉列表中小字体的替代品。

采纳答案by Kimball Robinson

I have a few options available that I am aware of thus far. The best I can come up with is this:

到目前为止，我有一些可用的选项。我能想到的最好的是：

Avoid directly using formula-based named ranges. Instead, define a subroutine that will check whether the defined range "demoRange" should be changed from what its current value is. Run this subroutine on the workbook_open and sheet3_deactivate events. If needed, prompt the user to ask if it's all right to update the named range. [edit] The macro that updates "demoRange" could probably just copy from a "demoRange_FormulaBased" named range into "demoRange" which would be static. [/edit]

避免直接使用基于公式的命名范围。相反，定义一个子程序来检查定义的范围“demoRange”是否应该从它的当前值改变。在 workbook_open 和 sheet3_deactivate 事件上运行此子例程。如果需要，提示用户询问是否可以更新命名范围。[编辑] 更新“demoRange”的宏可能只是从名为“demoRange_FormulaBased”的范围复制到静态的“demoRange”中。[/编辑]

This solution works well because you can keep using the linkedcell property, you don't have to use VBA to populate the comboboxes, and the named range can still be used for whatever other purposes it already had. Avoid using the onchange event to run this new subroutine, since it might end up being triggered thousands of times if a user opens the Find/Replace dialog and chooses "Replace All".

这个解决方案很有效，因为您可以继续使用链接单元属性，您不必使用 VBA 来填充组合框，并且命名范围仍然可以用于它已有的任何其他目的。避免使用 onchange 事件来运行这个新的子程序，因为如果用户打开“查找/替换”对话框并选择“全部替换”，它可能最终会被触发数千次。

回答by Dick Kusleika

My advice is to never use ListFillRange and LinkedCell. They are just trouble. Fill your listbox with List and use the Change event to write to the cell. Somewhere, maybe the Workbook_Open event, fill the listbox

我的建议是永远不要使用 ListFillRange 和 LinkedCell。他们只是麻烦。用 List 填充列表框并使用 Change 事件写入单元格。某处，也许是 Workbook_Open 事件，填充列表框

Private Sub Workbook_Open()

    Sheet2.ListBox1.Clear
    Sheet2.ListBox1.List = Sheet1.Range("demoRange").Value

End Sub

Then in the change event in the Sheet2 module, check that something was clicked and write it to the cell

然后在 Sheet2 模块中的更改事件中，检查是否单击了某些内容并将其写入单元格

Private Sub ListBox1_Change()

    If Me.ListBox1.ListIndex >= 0 Then
        Sheet2.Range("A1").Value = Me.ListBox1.Value
    End If

End Sub