使用 VBA 在 Excel ComboBox 中动态设置 ListFillRange

Dynamically set ListFillRange in Excel ComboBox using VBA

提问by firedrawndagger

I tried doing something like:

我尝试做类似的事情：

 cmbMyBox.ListFillRange = "E2"

But the combobox does not seem to populate.

但组合框似乎没有填充。

采纳答案by firedrawndagger

Ok, don't mean to answer my own question again but this ListFillRange property on combobox for Excel was absolutely maddening. Here's the final code that I implemented.

好的，我不想再次回答我自己的问题，但是 Excel 组合框上的这个 ListFillRange 属性绝对令人抓狂。这是我实现的最终代码。

   Sheet1.Range("E3").CurrentRegion.Select
   Dim example as Range
   Set example = Selection

   With cmbMyBox
     .ListFillRange = example.Address(0, 0, x1A1, True)
   End With

The trouble here was that I was trying to dynamically set the combobox using a dynamic range which changes depending on what user inputted values were given. As far as I understand I couldn't use a named range because named ranges are FIXED, e.g (A3:Z20) and cannot be adjusted.

这里的问题是我试图使用动态范围来动态设置组合框，该范围根据给定的用户输入值而变化。据我所知，我不能使用命名范围，因为命名范围是固定的，例如 (A3:Z20) 并且无法调整。

回答by Bill Splate

First of all, for trying to set the listfillrange from VBA, you need to include the '=' sign, like this:

首先，为了尝试从 VBA 设置 listfillrange，您需要包含 '=' 符号，如下所示：

combobox.ListFillRange = "=E3:E13"

and not combobox.ListFillRange = "E3:E13", this won't work.

而不是combobox.ListFillRange = "E3:E13"，这是行不通的。

You could have a dynamic named range, for example:

您可以有一个动态命名范围，例如：

listItems: "=Sheet1!$A:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))"

Use VBA to set the ListFillRange like this: combobox.ListFillRange = "=listItems"Again, use the '='sign!!

使用 VBA 像这样设置 ListFillRange：combobox.ListFillRange = "=listItems"再次使用'='符号！！

This dynamic range listItems grows/shrinks dynamically, depending on what values you have in column A.

此动态范围列表项会动态增长/缩小，具体取决于您在 A 列中的值。

I know I'm answering really late, but I noticed a lot of people thinking named ranges always have to be fixed, while they can be dynamic as well...

我知道我回答得很晚，但我注意到很多人认为命名范围总是必须固定的，而它们也可以是动态的......

How do you do this? In Excel 2007 and higher, you go to the ribbon-tab "Formulas"and click on the button "Name Manager" Instead of selecting cells and giving them a name in that upper-left box, you can manage all your defined named ranges here. Create a new one, and give it the value (without quotes):

你怎么做到这一点？在 Excel 2007 及更高版本中，您转到功能区选项卡"Formulas"并单击“名称管理器”按钮，而不是选择单元格并在左上角的框中为其命名，您可以在此处管理所有定义的命名范围。创建一个新的，并给它值（不带引号）：

"=Sheet1!$A$1:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))".

"=Sheet1!$A$1:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))".

There you go...

你去...

P.S. When you let the dynamic named range change, you should use VBA to re-set the .ListFillRange such that the combobox will refresh its list-items.

PS 当您让动态命名范围更改时，您应该使用 VBA 重新设置 .ListFillRange 以便组合框刷新其列表项。

回答by bonkers

Private Sub ComboBox1_Change()

Me.ComboBox2.ListFillRange = "=" & ComboBox1.Value

End Sub

This also works. But you have to do either the defined names with the index and counta as already suggested, or you can refer to them twice while in a table. What I mean by that, is make your data a table. Refer to the column you want as the title of your option from Combobox1 and add a one to the name, For example Fruits1, then define a secondary named range that refers to Fruits1and is called Fruits. FruitsFruits1

这也有效。但是您必须按照已经建议的那样使用索引和计数来定义定义的名称，或者您可以在表中引用它们两次。我的意思是，让你的数据成为一个表格。在 Combobox1 中引用您想要的列作为选项的标题，并在名称中添加一个，例如 Fruits1，然后定义一个引用Fruits1并称为Fruits的二级命名范围。 水果水果1

回答by ádám Bukovinszki

I was facing similar issue, not being able to populate the ActiveX ComboBox with list reference peeked from a cell's validation rule.

我遇到了类似的问题，无法使用从单元格验证规则中获取的列表引用来填充 ActiveX ComboBox。

Similarly to Firedrawndagger's own solution I went for manually translating the validation rule to a format that is understood by the .ListFillRange. I realised also, that it needs to be in a Workbook-scope format, otherwise the call will not work from other sheets.

与 Firedrawndagger 自己的解决方案类似，我手动将验证规则转换为 .ListFillRange 可以理解的格式。我也意识到，它需要采用工作簿范围的格式，否则调用将无法从其他工作表工作。

This works with All validation source formats, including: $A$1 / =NamedRange / =INDIRECT("Table1[Col2]") The translation was:

这适用于所有验证源格式，包括： $A$1 / =NamedRange / =INDIRECT("Table1[Col2]") 翻译为：

Dim xStr As String        
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
xStr = Split(Range(xStr).Address(, , , True), "]")(1)
'...other irrelevant code
.ListFillRange = xStr

回答by Yasser Elarabi

I had a similar problem, i had an ActiveX Listbox in the worksheet. What worked for me is this:

我有一个类似的问题，我在工作表中有一个 ActiveX 列表框。对我有用的是：

Sheets(1).OLEObjects("ListBox1").ListFillRange = "Sheet2!A1:C20"

ActiveX OLEObjects are pretty straight forward.

ActiveX OLEObjects 非常简单。

回答by Ivo Fontana

This is working fine on Excel 2010:

这在 Excel 2010 上运行良好：

I have a list of items in column "AN" that changes (get bigger/shorter) every week. I have created a variable called "c" which contains the number of items in the list. By using this variable, the ComboBox (positioned on cells S7:U7) will now dinamically show the items actually contained in the list (no empty spaces or missing items in the combobox).

我在“AN”列中有一个每周都会改变（变大/变短）的项目列表。我创建了一个名为“c”的变量，它包含列表中的项目数。通过使用此变量，组合框（位于单元格 S7:U7 上）现在将动态显示列表中实际包含的项目（组合框中没有空格或丢失的项目）。

Code:

代码：

Dim rng As Range

Set rng = ActiveSheet.Range("S7:U7")
ActiveSheet.DropDowns.Add(rng.Left, rng.Top, rng.Width, rng.Height).Select
With Selection
    .ListFillRange = "AN1:AN" & c
    .LinkedCell = "$V"
    .DropDownLines = 8
    .Display3DShading = False
End With

回答by user3598413

Alternatively, this is how I do it:

或者，这就是我的做法：

Define a range name that includes header and trailer rows plus one data row to start, say "DataList"

定义一个范围名称，其中包括标题行和结尾行以及要开始的一个数据行，例如“DataList”

Then define the following data range name sans the header and trailer records using the offset function.

然后使用 offset 函数定义以下数据范围名称，不包括头和尾记录。

Say "DataRange" = Offset(DataList,1,0,Rows(DataList)-2)

说 "DataRange" = Offset(DataList,1,0,Rows(DataList)-2)