Working with textboxes in VBA

[wpryan]

Hi All,

I am stumbling through working with textboxes in VBA. I found code that will generate the text box, and how to put it in the location I am looking for, but how to format the text? This is where I'm coming up short... The short bit of code I have is:

myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _    100, 100, 200, 50) _    .TextFrame.Characters.Text = "Test Box"

I would like to center the text in the text box (center and middle alignment). How to do so...?

Thanks in advance...

 

[Dan_W]

Hi

Using the Macro Recorder, it's pretty straightforward. After a little bit of cleaning up of the code, I managed to have it generate this:

Sub CreateTextBox()
    Dim MyTB As Shape
    Dim MyDocument As Worksheet
    Set MyDocument = Application.ActiveSheet
    Set MyTB = MyDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50)
    With MyTB
        .TextFrame2.TextRange.Text = "TestBox"
        .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .TextFrame.VerticalAlignment = xlVAlignCenter
    End With
End Sub

I assumed MyDocument was a reference to the Active Sheet.

 

[dmt32]

Hi,
try this update to your code

Sub AddShape()

    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50).TextFrame
           .Characters.Text = "Test Box"
           .HorizontalAlignment = msoAlignCenter
           .VerticalAlignment = msoAlignCenter
    End With
    
End Sub

Edit.

Dave

 

[wpryan]

Hi,
try this update to your code

Sub AddShape()

    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50).TextFrame
           .Characters.Text = "Test Box"
           .HorizontalAlignment = msoAlignCenter
           .VerticalAlignment = msoAlignCenter
    End With
  
End Sub

Edit.

Dave

Thanks for your quick response! Works like a charm... now next question (because there's always a follow up question)... How to add formatting to the text (font, color size)...?

 

[dmt32]

Thanks for your quick response! Works like a charm... now next question (because there's always a follow up question)... How to add formatting to the text (font, color size)...?

update as below

Sub AddShape()

    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50).TextFrame
           With .Characters
                .Text = "Test Box"
                .Font.Size = 24
                .Font.Color = vbRed
           End With
           .HorizontalAlignment = msoAlignCenter
           .VerticalAlignment = msoAlignCenter
    End With
    
End Sub

Dave

 

[wpryan]

update as below

Sub AddShape()

    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50).TextFrame
           With .Characters
                .Text = "Test Box"
                .Font.Size = 24
                .Font.Color = vbRed
           End With
           .HorizontalAlignment = msoAlignCenter
           .VerticalAlignment = msoAlignCenter
    End With
   
End Sub

Dave

Thanks a lot, again!

 

[dmt32]

Thanks a lot, again!

welcome

just in case you want to fill the shape with color then modify code as below

Sub AddShape()
    
    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50)
    
        .Fill.ForeColor.RGB = RGB(255, 255, 0)
        
        With .TextFrame
            With .Characters
                .Text = "Test Box"
                .Font.Size = 24
                .Font.Color = vbRed
            End With
            
            .HorizontalAlignment = msoAlignCenter
            .VerticalAlignment = msoAlignCenter
            
        End With
    End With
    
End Sub

Dave

 

[wpryan]

welcome

just in case you want to fill the shape with color then modify code as below

Sub AddShape()
   
    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50)
   
        .Fill.ForeColor.RGB = RGB(255, 255, 0)
       
        With .TextFrame
            With .Characters
                .Text = "Test Box"
                .Font.Size = 24
                .Font.Color = vbRed
            End With
           
            .HorizontalAlignment = msoAlignCenter
            .VerticalAlignment = msoAlignCenter
           
        End With
    End With
   
End Sub

Dave

...err what if I don't want a border around the textbox...?

 

[dmt32]

just set line visibility

Sub AddShape()
    
    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50)
    
        .Fill.ForeColor.RGB = RGB(255, 255, 0)
        
        .Line.Visible = msoFalse
        
        With .TextFrame
        
            With .Characters
                .Text = "Test Box"
                .Font.Size = 24
                .Font.Color = vbRed
            End With
            
            .HorizontalAlignment = msoAlignCenter
            .VerticalAlignment = msoAlignCenter
            
            
        End With
        
    End With
    
End Sub

 

[wpryan]

Thanks again! I'm just starting to dabble with objects, it's entirely a different beast!