Link cell to combo box

[Atishoo]

Need help fast!!
Im have meade a combo box from the forms menu (hads to be from the forms
menu as im using vba to dynamically change the list fill range according to
the active cell)
I linked the combo box to a worksheet cell but all it gives me is the number
of the row I select in the drop down not the actual item selected!
Have tried allsorts and am now desperate!!

 

[Dave Peterson]

I bet you could have used a combobox from the control toolbox toolbar to
accomplish the same thing.

But if the linked cell is A1 and the list is in a named range "myList", then you
could use another cell to get the value shown in the dropdown.

=if(a1=0,"",index(mylist,a1))

 

[JLatham]

That's the way the forms Combo control works. Work with it. You can set up
the cell that it's linked to in an "out of the way" location and use it from
there as an index into the same range that you set up for your combo list
source to return the actual text via a formula in the cell where you want the
text to appear.

Simple example: you set the linked cell up as D19. At the particular point
in time you're using your combo box, its data source is range I1:I12. A
formula such as this would display the proper text:
=INDEX(I1:I12,D19)
Your VBA code will need to alter the formula in that cell at the time it
alters the data source for the Combo box. Assuming you have that data source
range (I1:I12) in a variable called myListAddres, the code could look
something like this:

Worksheets("Sheet1").Range("D19").Formula = _
"=INDEX(" & myListAddress & ",D19)"

Hope this helps some.