Bernie,
Thanks for looking. The problem is that there are two different textbox
objects. One is a
VB textbox object (can be created by having the toolbar
"Control Toolbox" visible and selecting the "Text Box" icon) and the other
is an Drawing textbox object (can be created by having the toolbar "Drawing"
visible and selecting the "Text Box" icon). MS sure wants to make it
difficult....
If you look at the example: Set tb = ActiveSheet.TextBoxes("Text Box 1")
which is different if you were to use a Form TextBox. ie. Set tb =
ActiveSheet.TextBox1
Sorry about the confusion! But I'm asking about the Drawing TextBox.
Brett
The example I posted does work and the textbox object does support the
..Character property.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
BC,
Textboxes don't support the character by character formatting that cells
allow - at least, the standard textboxes don't have a .Character property.
HTH,
Bernie
MS Excel MVP
"BC" wrote in message
...
I have a spreadsheet cell that I would like to copy not only it's text
but
it's Font properties to a textbox object. Since a single cell could
have
several different font attributes per each character, how do you
accomplish
this without setting each character's font one by one.
Hopefully there is a better way!!!!
---------------------------------------------------------------------
Example code looks like (if needed):
Place some text into A1 on a spreadsheet and change some font attributes
(i.e. bold, size, boldness, color...) on some of the letters in the
text.
Add a textbox somewhere on the page (don't change the name, it should
default to "Text Box 1").
Below is the code:
Option Explicit
Sub TryIt()
Dim i As Integer, acell As Range, tb As TextBox, f As Font
Set acell = ActiveSheet.Range("A1")
Set tb = ActiveSheet.TextBoxes("Text Box 1")
'--- copy the text
tb.Text = acell.Text
'--- copy the font attributes one-by-one.... ugggg!!!!
For i = 1 To acell.Characters.Count
Set f = acell.Characters(i, 1).Font
With tb.Characters(i, 1).Font
.Bold = f.Bold
.Color = f.Color
.ColorIndex = f.ColorIndex
.FontStyle = f.FontStyle
.Italic = f.Italic
.Name = f.Name
.OutlineFont = f.OutlineFont
.Shadow = f.Shadow
.Size = f.Size
.Strikethrough = f.Strikethrough
.Subscript = f.Subscript
.Superscript = f.Superscript
.Underline = f.Underline
End With
Next
End Sub