CODEWITHSUNDEEP
excelvba
sooprise
sooprise

Reputation: 23177

Getting ComboBox Value In A Cell?

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

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

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

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

Upvotes: 2

Views: 36394

Answers (3)

AJ Geringer
AJ Geringer

Reputation: 11

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.

Upvotes: 1

Fink
Fink

Reputation: 3436

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.

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.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91306

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.

Upvotes: 0

Related Questions