指向基于公式的命名范围的Excel combobox listfillrange属性有问题 [英] Excel combobox listfillrange property pointing at a formula-based named range has issues

问题描述

我想我遇到过其他错误，但是Excel中的ActiveX组合框对象的ListFillRange引用了基于公式的命名范围（定义名称）甚至可能是因为Excel崩溃，但是现在所发生的一切都是随着工作簿中任何一个单元格被改变而触发的combobox_change（）事件。

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

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

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

1. 创建一个新的工作簿。在Sheet3上，创建一个小表格3列，跨越几行。


2. 使用此公式（或等价物）创建一个命名范围：= OFFSET（Sheet3！$ A $ 2：$ C $ 36,0,0，COUNTA（Sheet3！$ A：$ A），COUNTA（Sheet3！$ 4：$ 4））要做到这一点，请使用Input> Name> Define。将范围命名为demoRange


3. 转到Sheet1并创建一个组合框，（它必须在单独的表单上）。 （使用控制工具箱菜单，不表单菜单）。


4. 点击设计模式按钮（蓝色三角形用铅笔），然后右键单击组合框，然后转到属性。


5. 在组合框的属性窗口中，更改ListFillRange属性，使其指向在步骤2（demoRange）中创建的命名范围。 / li>
   
6. 您可能希望将ColumnCount属性更改为3，ColumnWidths属性更改为50,50,50


7. 将linkedCell属性设置为单元格/>
   
8. 关闭属性窗口，双击组合框定义其change（）事件。


9. 在新的组合框的更改事件的子例程中放置一个Debug.Assert（false）或Msgbox（demo）行。


10. 退出设计模式


11. - 现在选择组合框中的项目。事件应该是第一次正常触发。 （如果您不执行此步骤，该错误将不会显示 - 必须在组合框中选择某些内容）


12. 在工作簿或任何其他打开的工作簿中编辑单元格[ /编辑]，在任何工作表和任何位置。每次编辑任何单元格（至少对我来说），运行组合框的onchange事件。

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

解决方案

我有几个可用的选项，我知道到目前为止。最好的办法是：

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

此解决方案运行良好，因为您可以继续使用linkedcell属性，您不必使用VBA来填充组合框，而命名范围可以仍然用于任何其他目的已经有了。避免使用onchange事件来运行这个新的子例程，因为如果用户打开查找/替换对话框并选择全部替换，则可能会触发数千次。

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

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.

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?

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 seems like 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, not the 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.

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.

解决方案

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]

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

这篇关于指向基于公式的命名范围的Excel combobox listfillrange属性有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！