#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Okay, thanks! I guess I didn’t see the programming forum.
Quote:
|
|
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 |