Set formula for Excel textbox directly

I am trying to use a textbox to show the results of a formula that concatenates and calculates various cells. I remember there was a little-known trick in Excel 2003 onwards were you could directly set the formula for a text box (i.e. not a reference or defined name).

To be clear I don't want to set the formula in a hidden cell and then link the textbox to a cell. There is a way to set the formula directly for the textbox but for the life of me I can't remember or discover how to do it.

I also do not want to use 'paste picture link' to show the value from a hidden cell.

If someone would jolt my memory I would be most grateful.

Thanks in advance.
LVL 3
AL_XResearchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

I think you are thinking of Adding a shape ( for example a rectangle like a textbox) and then add a reference (=A1) as the text of the shape.

This method does not accept formulas only references

Regards
0
AL_XResearchAuthor Commented:
Hi Rgonzo. No as I said I do not want to simply set a reference to a cell which supplies the actual value.
0
byundtMechanical EngineerCommented:
You could use a Worksheet_Change event macro to update the value displayed on the shape. This macro will run whenever a value is changed on the worksheet--and will update the value displayed in your shape.

For example, I created a named formula (just like creating a named range, except you have your concatenation formula there instead of a reference to a single cell). I then added the following code to the worksheet code pane:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim frmla As String
frmla = Application.Evaluate(ThisWorkbook.Names("NamedFormula").Value)
With Me.Shapes("Rectangle 1")
    .TextFrame.Characters.Text = frmla
End With
End Sub

Open in new window



Despite ability of the above workaround to meet all of your requirements, I would personally put a formula in a cell in a hidden row or column and update the value in the shape using a formula that points to that hidden cell.
NamedFormulaToShapeQ28688910.xlsm
0
AL_XResearchAuthor Commented:
I guess I must have remembered incorrectly - still I am sure I was able to set a formula for a text box.

Must have just been a cell link !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
It was my mistake and it is not possible to do what I was asking.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.