Inserting last calculated cell's value to text box

[shabutt]

Hi community members,

I have a text box in a sheet in which i want to automatically show the
result of last calculated cell's value. Any help will be most appreciated.

Regards.

 

[Gary''s Student]

Put this event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
Dim v As Variant
Set b9 = Range("B9")
Set act = ActiveCell
v = b9.Value
ActiveSheet.Shapes("Text Box 1").Select
Selection.Characters.Text = v
act.Select
End Sub

It places the last calculated value of cell B9 in the textbox.

 

[shabutt]

Thanks Gary''s Student for your solution. Here is what I did. I took advice
from this code:

'Option Explicit

'Dim rngLast As Range

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Not rngLast Is Nothing Then
'MsgBox "Current Address: " & Target.Address(0, 0) & vbCrLf & _
'"Last Address: " & rngLast.Address(0, 0)
'End If
'Set rngLast = Target
'End Sub
'http://www.pcreview.co.uk/forums/thread-1008656.php

and modified it for my solution:

Option Explicit

Dim rngLast As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Not rngLast Is Nothing Then
ActiveSheet.Shapes("TextBox 1").Select
Selection.Characters.Text = rngLast(1, 6).Value
End If
Set rngLast = Target
rngLast.Select
End Sub

As I am novice in vba, therefore the above code is based on trial and
error. The code did the trick although not quite what was required, i.e.,
last calculated cell.

The code is not perfect and I would like helping hands to make it run faster
and strip it off the unnecessay code.

Your help is requested.

 

[shabutt]

I also got here "http://www.rondebruin.nl/shape.htm" to resolve slow speed of
code but haven't got far. Have no idea what to do.

 

[Gary''s Student]

Now we are getting close!

Tell me what the (1,6) means in the:

Selection.Characters.Text = rngLast(1, 6).Value

statement.

 

[Dave Peterson]

When you refer to a range like this, it's one based.

It's the equivalent of:
rnglast.cells(1,6)
or
rnglast.offset(0,5)
(notice the numbers changed)

Chip Pearson has some notes written by Alan Beban that show some other ways to
address ranges:
http://www.cpearson.com/excel/cells.htm