Echo Contents of ActiveCell into a Text box.

  • Thread starter Thread starter ChrisR
  • Start date Start date
C

ChrisR

I want a Textbox to contain a text on a column of To Do statements - the one
the cursor is at.
I can make a textbox echo a given cell (but not a formula)
I can use a Custom function to read the current cell, but it only calculates
it once.
I cant make that cell echo or refer to the current ActiveCell dynamically
Can anyone help?
 
Two macros:

1. one to create the textbox
2. another to refresh it.

In a standard module:

Sub MakeBox()
'
' Macro1 Macro
' Macro recorded 9/17/2009 by James Ravenswood
'

'
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 358.5, 91.5, _
316.5, 167.25).Select
Selection.Characters.Text = ""
Range("A1").Select
End Sub

In the worksheet code area, the following event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Columns("D")) Is Nothing Then Exit Sub
v = Target.Value
Application.EnableEvents = False
ActiveSheet.Shapes(1).Select
Selection.Characters.Text = v
Target.Select
Application.EnableEvents = True
End Sub


This is coded for column D
 
You seem to have all the pieces; it may help to see the code you have so that
we can make it more dynamic. But I suspect that in the code you have to get
the textbox echo a given cell, all you need to do is change part of the
statement to something like:
= ActiveCell.Value
instead of whatever hard cell address you have.

You can even wrap that up in an IF statement to 'filter' on the column, as

If ActiveCell.Column = Range("A1").Column ' change A to real column ID
'your code to assign ActiveCell.Value to the text box here
End If
 
Back
Top