Well it is certainly challenging, and that's because the formatting in atext box isn't (so far as I know) readily copied excpet in another text box.
The elements of a possible but long solution as specified include these elements:
Each Textbox is a Shape on a sheet amd can be identified by name, e.g.
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
Debug.Print sh.Name
'debug.Print sh.
Next
They could also be identified by index; ActiveSheet.Shapes(index) once you knew the index and could be sure it would not change as you edit the worksheet.
Next you need to understand for the formatting is done. I recorded this with font name and size and color as a sample of what's involved.
ActiveSheet.Shapes("Text Box 2").Select
Selection.Characters.Text = "This is formatted text" & Chr(10) & "so is this"
With Selection.Characters(Start:=1, Length:=1).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Characters(Start:=2, Length:=7).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Characters(Start:=9, Length:=9).Font
.Name = "Bodoni MT Black"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Characters(Start:=18, Length:=1).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Characters(Start:=19, Length:=15).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
In theory one could parse and record the stored text for repeating in the target box, but I would look for a different approach. Sorry!
If you do manage it, the code would be worth sharing here.
Bookmarks