在单元格中获取ComboBox值？

I have a named comboBox, let's call it: "comboBox1"

我有一个命名的comboBox，我们称之为：“comboBox1”

I want to reference the value of comboBox1 from a cell.

我想从一个单元格引用comboBox1的值。

=if(comboBox1.Value=1,1,0)

The idea above is what I'm looking for. I know I can attach an even to comboBox1, which populates a cell, which can be read by other cells, but that just introduces more moving parts and complexity.

上面的想法是我正在寻找的。我知道我可以将一个偶数附加到comboBox1，它填充一个单元格，可以被其他单元格读取，但这只会引入更多移动部件和复杂性。

This has to be possible, right? Any help would be great, thanks!

这必须是可能的，对吗？任何帮助都会很棒，谢谢！

3 个解决方案

#1

Excel allows a cell link on both an ActiveX and a forms doropdown (combo). This will write the value to a cell without any code.

Excel允许ActiveX和表单doropdown（组合）上的单元格链接。这将在没有任何代码的情况下将值写入单元格。

#2

I think something like this is possible.

我觉得这样的事情是可能的。

For your combobox change event, you will need to trigger a recalculation:

对于组合框更改事件，您需要触发重新计算：

Private Sub ComboBox1_Change()
    Application.Calculate
End Sub

Next, you will need to add a custom user defined function. The important piece of this the Application.Volatile line. This will make sure its recalculated, after any calculation.

接下来，您需要添加自定义用户定义的函数。 Application.Volatile行的重要部分。这将确保在任何计算后重新计算。

Function GetComboVal(cmbName As String) As String

    Application.Volatile 'will always recalculate

        Dim cmb As OLEObject

        Set cmb = Sheet1.OLEObjects(cmbName)

        GetComboVal = cmb.Object.Value
    End Function

So in your cell, you will need to use a call like this:

所以在你的单元格中，你需要使用这样的调用：

=if(GetComboVal("ComboBox1")=1,1,0)

The Downside to this technique is that if your worksheet has many calculations, it could take a while to recalculate.

这种技术的缺点是，如果您的工作表有很多计算，则可能需要一段时间才能重新计算。

#3

Good Afternoon,

下午好，

There is a much easier way to link a cell to a combo-box. With-in the properties of the ComboBox, above ListFillRange is Linked Cell. You would just designate this cell to whatever you want your combobox value to equal too.

将单元格链接到组合框有一种更简单的方法。使用ComboBox的属性，ListFillRange上面是Linked Cell。您只需将此单元指定为您希望组合框值相等的任何单元格。

#1