Copy range Cell text and font properties to a Textbox

[BC]

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

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

BC,

My apologies. It looks like you do need to do it property-wise, as your
code shows.

HTH,
Bernie
MS Excel MVP