I cannot post my code because it is company property, but the issue is that I would like to enter a cell formula so that it automatically updates when particular other cells change.
I have two columns, one for prices in USD and another for another currency. The other currency column is a simple "=SUM..." but the currency conversion to USD is always changing, and the prices are large enough that these changes matter. As such, I have the user enter the conversion value in the activex textbox, and I want to set the formula of the USD cells to be the price in USD, so obviously I need to get this conversion value, but I cannot just type in the textbox name, that does not work.
I am using VBA, but I would like to enter this directly into the cell as "=FORMULA", where formula contains the activeX textbox value, so that it automatically updates.
As far as I have found in VBA, if I want to assign a formula to a cell, I have to do so within a sub or function, so I have to call the sub every time I want to update the cells, but I want this to be as automatic as Excel is for other cell formulas.
I have been playing with Worksheet_Change and considered worksheet_open but have had other issues. For example, there are many different cells that could change that I have to monitor for change, and I cannot simply monitor the price column in the other currency for change because when cells update from a formula, Worksheet change does not show those cells as having changed.
Maybe this is as simple as setting the cell formulas on worksheet_open, but I also have not had luck with getting cell.formula to even work. I could discard the textbox for a regular cell which would totally solve this problem, but I also have a textbox_lostfocus sub that I need to run, and I don't know how to do that for a cell.
Sorry for the long post, in summary, how can I assess a textbox value for use in a cell formula ideally without using vba?
