Microsoft Office Forums - View Single Post - Using VBA Macros to Create Shapes with Text

mandersen04 · 01-26-2019, 12:34 PM

Hi All,

I'm creating a VBA macro for a friend of mine. She's a camp counselor during the summer, and she wants a macro that will automatically create awards for the different competitions she hosts. She wants the macro to create a shape with the worksheet name in that shape (e.g. First Prize).

I already have something simple cooked up. This is what I have so far:

Code:

Sub Certificate()

' Certificate Macro
'
' Keyboard Shortcut: Ctrl+Shift+C

    Dim s As Shape
    Dim ws As Worksheet

    Set ws = ActiveSheet

'add a rectangle
    Set s = ws.Shapes.AddShape(msoShapeRoundedRectangle, 50, 75, 560, 280)

'make it white
    s.Fill.ForeColor.RGB = RGB(255, 255, 255)

'show text within it
    s.TextFrame.Characters.Text = "Congratulations!" & vbNewLine & vbNewLine & ws.Name & "!"
    s.TextFrame.Characters.Font.ColorIndex = 1
    s.TextFrame.Characters.Font.Size = 36
    s.TextFrame.HorizontalAlignment = xlHAlignCenter
    s.TextFrame.VerticalAlignment = xlVAlignTop
End Sub

As you can see, I used the vbNewLine function to create two hard Returns between "Congratulations!" and "First Prize!", both of which are centered. The shortcoming of this is that I can't add other strings of text in the corners of the shape, such as the date and the counselor's signature (as well as the lines where you would put those things). The macro so far will only let me enter one text string at a time.

So what can I do to get around that? Do I need to create text boxes within the shape? What kinds of commands would that macro entail? To that end, is there a way I can choose precise dimensions for these text boxes without going through trial and error? I'm still not even sure what the dimension numbers represent in relation to the rest of the shape or the spreadsheet, so any help on that would be great.

Finally, is there a way that I can make the font size conditional based on the size of the shape? I assume that would be difficult if I'm using text boxes, but maybe there's a way to make the text box size conditional based on the size of the shape as well.

Beginning VBA coder here, so thank you for your patience.