vba vba动态填充组合框

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9220970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 15:11:47  来源:igfitidea点击:

vba dynamical filling combo boxes

excelvbacomboboxrange

提问by DevilWAH

Here is the set up, (all testing code)

这是设置,(所有测试代码)

I have a combo box on sheet one called "Devmod"

我在第一张纸上有一个名为“Devmod”的组合框

I have created a module called testing and with in it created the following sub

我创建了一个名为 testing 的模块,并在其中创建了以下子

Sub setcomb()

    Sheet1.Devmod.ListFillRange = Range(Sheets("Device_info").Range("l3"), _
                      Sheets("Device_info").Range("l3").End(xlDown)).Select
End Sub

this runs OK but does not fill the combo box with any data (although it does select the right cells I want)

这运行正常,但没有用任何数据填充组合框(尽管它确实选择了我想要的正确单元格)

Now I know I can do this be creating a name range and then assigning this to the combo box.

现在我知道我可以通过创建名称范围然后将其分配给组合框来做到这一点。

but what I am trying to do is when the user click a "update button" each combo box is updated with the values entered.

但我想要做的是当用户单击“更新按钮”时,每个组合框都会使用输入的值进行更新。

In logical terms this would be

从逻辑上讲,这将是

Select the range of cells from the Device_info sheet, Start at cell L3 and work out the last cell used. Assign these values to the combo box.

从 Device_info 表中选择单元格范围,从单元格 L3 开始并计算出最后使用的单元格。将这些值分配给组合框。

can I do this with one or two lines of code?

我可以用一两行代码做到这一点吗?

Cheers

干杯

DevilWAH

魔王

ANSWERMy simple function ends up as

ANSWER我的简单函数最终为

    Function setcomblist(wsheet As String, startrng As String, Optional endrng As Variant)

'used to populate comboboxes from a dynamic lenth range taking named sheet and starting cell as argument.
'use: combobox.ListFillRange = setcombolist("sheetname", "startcell")

If IsMissing(endrng) Then

setcomblist = Range(Sheets(wsheet).Range(startrng), Sheets(wsheet).Range(startrng).End(xlDown)).Address(External:=True)

Else

setcomblist = Range(Sheets(wsheet).Range(startrng), Sheets(wsheet).Range(endrng)).Address(External:=True)
End If

End Function

Thanks agin for the help people

感谢 agin 帮助人们

回答by Tim Williams

Dim rngList as range

With Sheets("Device_info") 
   Set rngList = .Range(.Range("l3"),.Range("l3").End(xlDown))
End With

Sheet1.Devmod.ListFillRange = rngList.Address(,,,true)

回答by mischab1

ListFillRangeis expecting a range address as a string. If you want to both select the range and assign it to the combo box you have to do that as two separate lines of code. Also, because your range of cells is on another worksheet, you need to give the range's external address. (Otherwise the combo box will think you mean the range on it's worksheet.)

ListFillRange期望范围地址作为字符串。如果您想同时选择范围并将其分配给组合框,则必须将其作为两行单独的代码进行。此外,由于您的单元格范围位于另一个工作表上,因此您需要提供该范围的外部地址。(否则组合框会认为您指的是其工作表上的范围。)

Sheet1.Devmod.ListFillRange = Range(Sheets("Device_info").Range("l3"), _
                  Sheets("Device_info").Range("l3").End(xlDown)).Address(External:=True)

For clarity, I'd rewrite it as follows.

为了清楚起见,我将其重写如下。

Sub setcomb()
    Dim rng as Range
    Set rng = Sheets("Device_info").Range("L3")
    Sheet1.Devmod.ListFillRange = Range(rng, rng.End(xlDown)).Address(External:=True)
End Sub

On the other hand, you can also do this with a dynamic named range. I don't recall how to create one off the top of my head, I'll look it up and add it later.

另一方面,您也可以使用动态命名范围来执行此操作。我不记得如何在我的头顶上创建一个,我会查找并稍后添加。



For a dynamic named range, enter something like this in Refers To.

对于动态命名范围,请在Refers To.

=OFFSET(Device_info!$L$3,0,0,COUNTA(Device_info!$L$3:$L$303),1)

=OFFSET(Device_info!$L$3,0,0,COUNTA(Device_info!$L$3:$L$303),1)

The COUNTAfunction needs to include the max number of rows you want to allow. The example above creates a dynamic named range that can grow up to 300 rows. And as my trusty 'Excel Hacks' book says:

COUNTA函数需要包含您想要允许的最大行数。上面的示例创建了一个动态命名范围,可以增长到 300 行。正如我可信赖的“ Excel Hacks”一书所说:

When defining the range for COUNTA, resist the temptation to include an entire column of data so you do not force the COUNTAfunction to count potentially thousands of unnecessary cells.

在为 定义范围时COUNTA,请抵制包含一整列数据的诱惑,这样您就不会强制COUNTA函数计算潜在的数千个不必要的单元格。