Set cell value in Excel with vaule from Combo Box.

[Channah]

I have a Combo Box in my Excel sheet that I would like to have auto-populate other cell below it. Any ideas?

 

[SkipVought]

Hi,

Are you using a Form combobox or Toolbox combobox?

With the latter, in the Combobox1_Change event...

  with combobox1
    .topleftcell.offset(2,0).value=.value
  end with

assuming that the cell you want to populate is 2 rows below the cell at the top left corner of the combobox.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Channah]

I have the combobox adding a value to the cell. Unfottunately it is adding the list position number of the value... for example: It is displaying 1 instead of Excel. I need it to display Excel. Also it is a Form Combo Box.

Thanks

 

[SkipVought]

If you were to use the toolbox control, it would do that.

The number in the cell is the list index. so you can take the list index and lookup the value using the INDEX function.

=INDEX(YourList,ListIndex,1)

If you set the ListFillRange and LinkedCell in the toolbox combobox, you'll get the value instead of the list index ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Channah]

Thanks but I am still lost on this one. I rarely use excel for this type of thing. I cannot get the index number to go away. I have tried to use the Index function as above but I am going to have to claim ignorance on this one. Any further detail would be greatly appreciated. Thanks

 

[SkipVought]

Using a combobox from the Forms menu, you cannot get the list index (cell link) to go away. You have to use the list index to get the corresponding list text.

Example -- assume a list in col a

apple
lemon
orange

your combobox cell link is in B1 and has a value of 2

your formula is is C1
=INDEX(A1:A3,B1,1) and the value is lemon

does this help?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com