How to define ActiveX TextBox's LinkedCell property with a formula?

[Pomee4]

Hello,

I'm trying to make a search function, what looks for the entered value in an array, and links a TextBox to the search results neighbour cell.

I have the formulas needed to get the row, column or address of the cell that should be linked, but I can't select a linked cell as a formula. When I enter a function to the properties, it doesn't apply.

Is there any workaround to this? I want the TextBox's linked cell change to select the correct cell depending on the input.

I'm even okay with having to click a "seach" button that loads the reqired values to a vba, if that is the only solution.

Thank you for your help in advance.

Kontaktok.xlsb
	A	B	C	D
1	result	Description	Name	Latin name
2	apple	Value of top TextBox	banana	Musa sapientum fixa
3	2	search for A2's row at D2:F5 =MATCH(A2;C2:C7;0)	apple	Malum
4	2	Allways 2nd column in D2:F5 =2	tomato	lycopersici Susceptibility
5	Malum	Value of A3 row and A4 column =INDEX(C2:D7;A3;A4)	carrot	Carota
6	$D$3	Location of A3 row and A4 column in D2:F7 =CELL("address";INDEX(C2:D7;A3;A4))	lemon	Citrea
7		Text Box that should be linked to A6's value as address ??????????	potato	Capsicum annuum
Sheet1

Cell Formulas
Range	Formula
A3	A3	=MATCH(A2,C2:C7,0)
A4	A4	=2
A5	A5	=INDEX(C2:D7,A3,A4)
A6	A6	=CELL("address",INDEX(C2:D7,A3,A4))

 

[6StringJazzer]

A linkedcell can be a range or a name, but it can't be a formula. A linkedcell has to work two ways: when a user types in the box, that content goes to the linkedcell and that's not possible with a formula.

I would use Worksheet_Change to detect changes in A3 and A4, and when either changes, update the linkedcell property of the textbox.