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

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

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

BC,

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

HTH,
Bernie
MS Excel MVP


"BC" wrote in message
.. .
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