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

Pomee4

New Member
Joined
Apr 9, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.

mYdtO.png


Kontaktok.xlsb
ABCD
1resultDescriptionNameLatin name
2appleValue of top TextBoxbananaMusa sapientum fixa
32search for A2's row at D2:F5 =MATCH(A2;C2:C7;0)appleMalum
42Allways 2nd column in D2:F5 =2tomatolycopersici Susceptibility
5MalumValue of A3 row and A4 column =INDEX(C2:D7;A3;A4)carrotCarota
6$D$3Location of A3 row and A4 column in D2:F7 =CELL("address";INDEX(C2:D7;A3;A4))lemonCitrea
7Text Box that should be linked to A6's value as address ??????????potatoCapsicum annuum
Sheet1
Cell Formulas
RangeFormula
A3A3=MATCH(A2,C2:C7,0)
A4A4=2
A5A5=INDEX(C2:D7,A3,A4)
A6A6=CELL("address",INDEX(C2:D7,A3,A4))
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
842
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,381
Messages
5,635,924
Members
416,887
Latest member
SheriE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top