Combo box value

[VegasNath]

I have a combo box which when right clicked, states =EMBED("Forms.ComboBox.1","").

The combo box is linked to a range of dates named DateChoice.

How can I get to the selected DateChoice in a formula?

[HansV]

Right-click the combo box and select Properties from the popup menu.
Enter a cell address in the LinkedCell property. If the cell is in the same sheet as the combo box, it is sufficient to use e.g. B3, if the cell is in another sheet, use e.g. Sheet2!B3.
The specified cell will automatically be filled with the selected item.

[VegasNath]

Thanks Hans, that was my existing setup but I was hoping to concatenate the value with text, without using a seperate cell. It's no big deal, I can work with that.

[HansV]

Here is a quick 'n dirty solution. Let's say the combo box is named ComboBox1.

Copy the following function into a standard module:

Code: Select all

Function GetComboValue()
  GetComboValue = ActiveSheet.ComboBox1
End Function

You can now use a cell formula like this:

="The value is "&GetComboValue()

[VegasNath]

Thanks Hans, that is useful. However, I need to perform calculations based on the output date, but I don't seem to be able to. Using your function, I have:

GetComboValue = Format(ActiveSheet.ComboBox1.Value, "ddd dd mmmm yyyy")

In J3: =GetComboValue()...... returns date, formatted as expected.
In J4: =J3+1...... returns #VALUE

[HansV]

Format returns a text value, not a date. Change the function to

Code: Select all

Function GetComboValue()
  GetComboValue = CDate(ActiveSheet.ComboBox1)
End Function

and format J3, J4 etc. as ddd dd mmmm yyyy.

[VegasNath]

Sorry to be a pain, but:

GetComboValue = CDate(ActiveSheet.ComboBox1)

=GetComboValue()

This is returning #VALUE

Also, I notice that the function does not automatically update when the combobox is changed. How can I do that?

[HansV]

No idea why you get #VALUE, it works for me.

To make the function update automatically, add the line

Application.Volatile

above

GetComboValue = ...

[VegasNath]

I seem to remember having problems with CDATE in the past.
The Application.Volatile is not working for me either.

Just not my day.

[HansV]

Could you post a small sample workbook demonstrating the problem?