How to get value from combo box in Excel?

[MottoK]

Hi - hopefully this should be a quick one:

I have one Combo box (chosen from the Form toolbox) in my excel spreadsheet - all I want to be able to do is get the value chosen from the combo box by using the formula/Function area (not having to use the likes of Index, Offset etc) something like:

=IF(Campaign_Drp.Value="Test","This is working","Nope")

I just keep returning #NAME?

Can anyone help?

Cheers.

 

[Rofeu]

Hi,

you can't reference the object directly like that in a formula.

What you can do is set the LinkedCell property and reference that cell in your formula.

Cheers,

Roel

 

[SkipVought]

Hi,

"...not having to use the likes of Index..."

Well, I guess yer up the creek without a paddle, if you don't use the Index Function, because, all you get in the linked cell is the index.

I always code the index function in an adjacent cell to the linke cell so that I always get the VALUE.

Skip,

_[red][/red]

 

[SkipVought]

...continuing...

LinkedCell: I NAME this cell SelectedCampaignIndex
Adjacent cell: I NAME this cell SelectedCampaign and return a value using the INDEX function

Then in usage...
[tt]
=IF(SelectedCampaign="Test","This is working","Nope")
[/tt]

Skip,

_[red][/red]

 

[Rofeu]

I beg to differ, Skip, the linked cell will take the actual value of the combobox, not the index.

Cheers,

Roel

 

[SkipVought]

Not a Forms Toolbar, which the OP seems to be using.

Skip,

_[red][/red]

 

[Rofeu]

Ah, his use of the word 'Toolbox' made me believe he was using ActiveX.

You are, of course, correct when it comes to a Forms toolbar object.

Cheers,

Roel

 

[yogia]

Hi Roel:

As Skip has reiterated the OP is using the COmboBox from the Forms Toolbar, so the Cell_link will have the INDEX to the Input_range.

As you stated ... if the OP was using the ComboBox from the ControlBox, then the LinkedCell will have the actual value of the selection from the ListFillRange.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Sorry for my redundant post Roel ... you beat me to it!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Rofeu]

no worries, Yogia, we all have that now and again.

 

[MottoK]

Thanks for the help.

 

[SkipVought]

MottoK,

Bookends?

"Thanks for the help."

Exactly WHAT was helpful? Maybe someone browsing this forum would like to know exactly how you were helped. You know that others may have similar unposted questions.

Skip,

_[red][/red]

 

[MottoK]

I said "thanks for the help" because now I know I can't do what I was trying to do - what else can I say?

I used the INDEX method before I asked the question and now I'm continuing to use it.

 

[SkipVought]

Looks like you just said what the final disposition was: use the INDEX function.

That, in the context of your original post, is helpful feedback to other readers.

Skip,

_[red][/red]

 

[MottoK]

Did like the naming cell thing though - hadn't done that before.

Cheers SkipVought you've saved my as* on many occasions!