Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-26-2019, 12:34 PM
mandersen04 mandersen04 is offline Windows 10 Office 2016
Novice
 
Join Date: Sep 2018
Posts: 4
mandersen04 is on a distinguished road
Default Using VBA Macros to Create Shapes with Text

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.

Last edited by Pecoflyer; 01-27-2019 at 12:42 AM.
Reply With Quote
  #2  
Old 01-27-2019, 12:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Hi and welcome
- to get faster and better answers it is always advisable to post in the proper forum ( programming in this case) I moved it for you
- also when posting code please wrap the code with code tags (the #button). Code structure is preserved and it's easier to copy if need be ( I also did it for you)
Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 01-27-2019, 10:52 AM
mandersen04 mandersen04 is offline Windows 10 Office 2016
Novice
 
Join Date: Sep 2018
Posts: 4
mandersen04 is on a distinguished road
Default

Okay, thanks! I guess I didnít see the programming forum.

Quote:
Originally Posted by Pecoflyer View Post
Hi and welcome
- to get faster and better answers it is always advisable to post in the proper forum ( programming in this case) I moved it for you
- also when posting code please wrap the code with code tags (the #button). Code structure is preserved and it's easier to copy if need be ( I also did it for you)
Thanks
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Issues with text in circle shapes that I am having damefrombrum Word 0 12-01-2015 08:05 PM
When I create a new macro in Word 2013, it overwrites my previously saved macros. lilihildreth Word VBA 3 02-05-2015 03:27 PM
Putting text in color shapes dianabanana Word 1 04-07-2014 08:07 PM
Reflowing text in shapes using ATT Webmeeting PkB123 PowerPoint 0 02-05-2014 10:05 AM


All times are GMT -7. The time now is 10:02 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft