Excel中的ComboBox返回值，而不是索引(ComboBox in Excel to return value, not index)

Excel中的ComboBox返回值，而不是索引(ComboBox in Excel to return value, not index)

我在Sheet1中有一系列重复日期！$ A：$ A. 这些我作为唯一项添加到组合框（cb）与VBA。 不幸的是，当我选择一个项目（cb中的日期）时，项目的索引将返回到链接的单元格中。 有没有办法返回日期而不是索引，或填写cb作为项目对（日期，日期）？

A B C ____D_________ 1 date val1 val2 |03/09/2015 |V | 2 03/08/2015 13 2.4 | __|_ | 3 03/08/2015 17 4.6 4 03/08/2015 11 2.5 5 03/09/2015 9 1.5 6 03/10/2015 4 3.2 7 03/10/2015 12 3.4

在上面的例子中，我的cb将在D1中返回2（这是底层的链接单元格），但我希望返回日期。 有一种“简单”的方法来实现这一目标吗？

这似乎不起作用：

With wsSheet2.Shapes("DatePick").ControlFormat .RemoveAllItems For Each vaItem In ncData .AddItem vaItem, vaItem '<--- add an item pair Next vaItem End With

谢谢

I have a range of duplicate dates in Sheet1!$A:$A. These I add as unique items to a combo box(cb) with VBA. Unfortunately, when I select an item (date in the cb) the index of the item is then returned into the linked cell. Is there a way to return the date instead of the index, or fill the cb as item pairs (date , date)?

A B C ____D_________ 1 date val1 val2 |03/09/2015 |V | 2 03/08/2015 13 2.4 | __|_ | 3 03/08/2015 17 4.6 4 03/08/2015 11 2.5 5 03/09/2015 9 1.5 6 03/10/2015 4 3.2 7 03/10/2015 12 3.4

in the example above, my cb would return 2 in D1 (which is the underlying linked cell), but I would like the date to be returned. Is there a "simple" way to accomplish this?

This does not seem to work:

With wsSheet2.Shapes("DatePick").ControlFormat .RemoveAllItems For Each vaItem In ncData .AddItem vaItem, vaItem '<--- add an item pair Next vaItem End With

Thanks

最满意答案

尝试这个：

Sub TransferValue() Dim dd As DropDown, linkedcell As String linkedcell = Sheet1.Shapes("Drop Down 1").ControlFormat.linkedcell Set dd = Sheet1.Shapes("Drop Down 1").OLEFormat.Object Sheet1.Range(linkedcell) = dd.List(dd.Value) End Sub

我假设你使用的是Form Control因为你有一个LinkedCell 。 所以这基本上获取了LinkedCell地址，因此您可以使用相同的地址进行输出。 然后，您获取ComboBox或DropDown Object的值，并将其传递给LinkedCell地址。 您只需将此宏指定给ComboBox即可。 HTH。

Try this:

Sub TransferValue() Dim dd As DropDown, linkedcell As String linkedcell = Sheet1.Shapes("Drop Down 1").ControlFormat.linkedcell Set dd = Sheet1.Shapes("Drop Down 1").OLEFormat.Object Sheet1.Range(linkedcell) = dd.List(dd.Value) End Sub

I assumed you are using Form Control since you have a LinkedCell. So this basically gets the LinkedCell address so you can use the same address for output. Then you grab the value of the ComboBox or DropDown Object and pass it to the LinkedCell address. You just need to assign this Macro to your ComboBox. HTH.