I am trying to link a textbox to a cell that has a formula. Ideally the value
of the textbox changes everytime the value of the cell changes. But the
problem I am having is the the cell has a formula which get erased every time
the value changes.

The cells(J187) formula is as follows: =SUM(J69:J186)
TextBox9 displays the value of cell "Sheet2 J187"
I need it to change as cell J187 changes.
Any ideas

I have manage to sort the problem by adding the following code in sheet2. As
the value in cell "j187" changes it changes tha value in textbox9.

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Sheets("Sheet2").Range("j187").Copy
Sheets("Sheet2").Range("j188").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

"hoyos" wrote:

I am trying to link a textbox to a cell that has a formula. Ideally the value
of the textbox changes everytime the value of the cell changes. But the
problem I am having is the the cell has a formula which get erased every time
the value changes.

The cells(J187) formula is as follows: =SUM(J69:J186)
TextBox9 displays the value of cell "Sheet2 J187"
I need it to change as cell J187 changes.
Any ideas

A tiny trick!

1. create the text box (or any other shape from the drawing toolbar!)
2. click on the shape, but don't type in it
3. click in the Formula Bar and enter:

=J187

If you put the formula in the Textbox, Excel would treat it as just Text.
--
Gary''s Student - gsnu200908


"hoyos" wrote:

I am trying to link a textbox to a cell that has a formula. Ideally the value
of the textbox changes everytime the value of the cell changes. But the
problem I am having is the the cell has a formula which get erased every time
the value changes.

The cells(J187) formula is as follows: =SUM(J69:J186)
TextBox9 displays the value of cell "Sheet2 J187"
I need it to change as cell J187 changes.
Any ideas

Gary, I tried that but it did not like it at all.
The formula I'm using seems to work ok. I suppose I can just add more cells
tpo that code?

"Gary''s Student" wrote:

A tiny trick!

1. create the text box (or any other shape from the drawing toolbar!)
2. click on the shape, but don't type in it
3. click in the Formula Bar and enter:

=J187

If you put the formula in the Textbox, Excel would treat it as just Text.
--
Gary''s Student - gsnu200908


"hoyos" wrote:

I am trying to link a textbox to a cell that has a formula. Ideally the value
of the textbox changes everytime the value of the cell changes. But the
problem I am having is the the cell has a formula which get erased every time
the value changes.

The cells(J187) formula is as follows: =SUM(J69:J186)
TextBox9 displays the value of cell "Sheet2 J187"
I need it to change as cell J187 changes.
Any ideas

I have used that formula bar approach to putting a cell value in text boxes
without problem in the past, including after "upgrading" to 2007. But I have
a current spreadsheet with several such text boxes, and only 1 or 2 of them
work consistently. Most of them behave just as hoyos described: they seem to
work initially, but delete the formula and retain the original number after
cell values change. Any clues? Thanks.


"Gary''s Student" wrote:

A tiny trick!

1. create the text box (or any other shape from the drawing toolbar!)
2. click on the shape, but don't type in it
3. click in the Formula Bar and enter:

=J187

If you put the formula in the Textbox, Excel would treat it as just Text.
--
Gary''s Student - gsnu200908


"hoyos" wrote:

I am trying to link a textbox to a cell that has a formula. Ideally the value
of the textbox changes everytime the value of the cell changes. But the
problem I am having is the the cell has a formula which get erased every time
the value changes.

The cells(J187) formula is as follows: =SUM(J69:J186)
TextBox9 displays the value of cell "Sheet2 J187"
I need it to change as cell J187 changes.
Any ideas