Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-26-2019, 12:34 PM
mandersen04 mandersen04 is offline Using VBA Macros to Create Shapes with Text Windows 10 Using VBA Macros to Create Shapes with Text Office 2016
Novice
Using VBA Macros to Create Shapes with Text
 
Join Date: Sep 2018
Posts: 12
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 online now Using VBA Macros to Create Shapes with Text Windows 7 64bit Using VBA Macros to Create Shapes with Text Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 01-27-2019, 10:52 AM
mandersen04 mandersen04 is offline Using VBA Macros to Create Shapes with Text Windows 10 Using VBA Macros to Create Shapes with Text Office 2016
Novice
Using VBA Macros to Create Shapes with Text
 
Join Date: Sep 2018
Posts: 12
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
Using VBA Macros to Create Shapes with Text 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:16 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft