.TextFrame.Characters.Text property readOnly in function??

  • Thread starter Thread starter VB_Help
  • Start date Start date
V

VB_Help

I am trying to mirror the text in an Excell cell to the text
in a shape object using the code below.

Cell B3: = 5/5/03
Cell B4: = =SomeWB.XLS!tmp(B3)

Function tmp(dte As Range) As String
// thisShape shape object already exists
With ActiveSheet.Shapes("thisShape")
.TextFrame.Characters.Text = dte.Value
End With
tmp = "OK"
End Function


// sub is run using Marco | Run
Sub tmp() // thisShape shape object already exists
With ActiveSheet.Shapes("thisShape")
.TextFrame.Characters.Text = "mock date value"
End With
End Function


I use the same code in a Sub and a Function, but the dte.Value won't
display using the function. I even called the sub from a function an it
still does not work.
 
Generally, a function used in a worksheet can not alter values or other
aspects of the excel environment - they can only return a value to the cell
in which they are located. I would suspect this limitation is the source of
your problem. (calling a sub from a function doesn't get around this
either).

If you use a textbox from the forms toolbar, you can select it, go to the
formula bar and enter

=B3 <cr>
and the textbox will be linked to the cell. If it isn't a textbox, it still
will probably work for some other type shapes.

If it is a textbox from the control toolbox toolbar, then you can use the
linked cell property.
 
Back
Top