Make a text box like this using VBA

[treygon]

Does anyone know how to create a text box like this using VBA? I tried recording a macro while I made one, but it didn't record anything.

Any guidance would be greatly appreciated.

Trey

 

[Glory]

set a = ThisWorkbook.ActiveSheet.OLEObjects.Add _
        ("Forms.Textbox.1", _
        Height:=100
        Left:=50
        Top:=50
        Width:=200)
 
a = "Text" & "Arrow" ANSI character code (check charmap or online)

 

[pgc01]

Hi Trey

This code creates a textbox overlapping a range:

Sub AddTextbox()
Dim tbox As TextBox
 
' textbox will overlap, for example, range C3:H6
With Range("C3:H6")
    Set tbox = ActiveSheet.TextBoxes.Add(.Left, .Top, .Width, .Height)
End With
 
tbox.Text = "This is an example"
End Sub

About the formatting, remember this is a spreadsheet, not a word processor. This textbox does not have bulleted lists or other advanced formatting. You can, however, simulate it, by adding spaces at the beginning of a line and using a bullet from the Unicode map.

Remark: always post you excel version.

 

[AlphaFrog]

Here's a little more...

Sub Fancy_TextBox()

    Dim tbox As Shape, MyText As String

    MyText = " How do you make a textbox like this in Excel VBA" & vbLf & _
             "     " & Chr(149) & " So that it has indents" & vbLf & _
             "     " & Chr(149) & " and bullets like this" & vbLf & vbLf & _
             " Any help would be greatly appreciated."
    
    With Range("C3:H8")
        Set tbox = ActiveSheet.Shapes.AddTextbox( _
            Orientation:=msoTextOrientationHorizontal, _
            Left:=.Left, Top:=.Top, _
            Width:=.Width, Height:=.Height)
    End With
        
    With tbox.DrawingObject
        .Characters.Text = MyText
        With .Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
    End With
    Range("A1").Select
End Sub

 

[Glory]

All that and... well.

 

[treygon]

Here's a little more...

Sub Fancy_TextBox()

    Dim tbox As Shape, MyText As String

    MyText = " How do you make a textbox like this in Excel VBA" & vbLf & _
             "     " & Chr(149) & " So that it has indents" & vbLf & _
             "     " & Chr(149) & " and bullets like this" & vbLf & vbLf & _
             " Any help would be greatly appreciated."
    
    With Range("C3:H8")
        Set tbox = ActiveSheet.Shapes.AddTextbox( _
            Orientation:=msoTextOrientationHorizontal, _
            Left:=.Left, Top:=.Top, _
            Width:=.Width, Height:=.Height)
    End With
        
    With tbox.DrawingObject
        .Characters.Text = MyText
        With .Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
    End With
    Range("A1").Select
End Sub

Thanks AlphaFrog! Now, how can I add text formatting to specific lines or words in the text box. Using the above for an example, how would I make only the line "and bullets like this" the color blue?

Thank you much.

 

[AlphaFrog]

You're welcome. You should really thank everyone that contributed. My code was just built on theirs.

The text coloring code added below was obtained using the macro recorder.

Sub Fancy_TextBox()

    Dim tbox As Shape, MyText As String

    MyText = " How do you make a textbox like this in Excel VBA" & vbLf & _
             "     " & Chr(149) & " So that it has indents" & vbLf & _
             "     " & Chr(149) & " and bullets like this" & vbLf & vbLf & _
             " Any help would be greatly appreciated."
    
    With Range("C3:H8")
        Set tbox = ActiveSheet.Shapes.AddTextbox( _
            Orientation:=msoTextOrientationHorizontal, _
            Left:=.Left, Top:=.Top, _
            Width:=.Width, Height:=.Height)
    End With
        
    With tbox.DrawingObject
        .Characters.Text = MyText
        With .Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
[COLOR="Red"]        With .Characters(Start:=86, Length:=23).Font
[COLOR="Green"]'            .Name = "Arial"
'            .FontStyle = "Regular"
'            .Size = 10
'            .Strikethrough = False
'            .Superscript = False
'            .Subscript = False
'            .OutlineFont = False
'            .Shadow = False
'            .Underline = xlUnderlineStyleNone[/COLOR]
            .ColorIndex = [COLOR="Blue"]5[/COLOR]
        End With[/COLOR]
    End With
    Range("A1").Select
    
End Sub

 

[treygon]

Yes, thanks to all who help out on this. This is exactly what I needed. This forum is made great by people like those who responded here.