Keep formatted shape to use in other workbooks

cricket1001

cricket1001

New member
Joined
Aug 28, 2017
Messages
5
Reaction score
0
Points
0
Location
Lady Lake, FL
Excel Version(s)
2016
I get tired of having to reformat an arrow, or other shape, each time I want to insert and arrow in my Excel 2016 workbooks. Is there a way to format an arrow and save it somewhere that will show up somewhere in all worksheets so I just have to click it and the correctly formatted arrow will appear? Maybe to create a shortcut so every time I press Ctrl+something will bring up the arrow? Or maybe a way to add a menu item with my different formatted shapes that I use frequently? I know the drop-down list in insert>shapes has "Most Recently Used" to choose a shape. But I have to reformat the arrow every time I have to use the same formatted arrow I try to always use. I know if I am in the same workbook I can copy/paste the shape but I would think that there must be a way to insert the same formatted arrow when I start a new workbook.

Thanks
 
Record yourself a macro of you adding the shape and making modifications to it. Make sure you store it in the Personal Macro Workbook, you can alter the macro's name, and even assign it a keyboard shortcut at this point:
2019-12-14_235542.jpg
I got this:
Code: 
Sub Macro1()
    ActiveSheet.Shapes.AddShape(msoShapeRightArrow, 96, 45, 77.04, 38.16).Select
    Selection.ShapeRange.IncrementRotation 45
    Selection.ShapeRange.ScaleWidth 4.2542834891, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 0.4716981132, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset13
    With Selection.ShapeRange.Line
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorText1
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = 0
      .Transparency = 0
    End With
End Sub
Change the first line from the likes of:
Code: 
ActiveSheet.Shapes.AddShape(msoShapeRightArrow,[COLOR=#FF0000] 96, 45[/COLOR], 77.04, 38.16).Select
to like:
Code: 
ActiveSheet.Shapes.AddShape(msoShapeRightArrow, [COLOR=#FF0000]ActiveCell.Left, ActiveCell.Top[/COLOR], 77.04, 38.16).Select
so that the new shape will appear in the vicinity of the sheet's active cell.

Now you only have to select a cell and press your keyboard shortcut. It will apply to all workbooks.
When closing Excel, you may be asked if you want to save changes to the Personal macro workbook, when you should say yes.
 
Last edited:
You must log in or register to reply here.
Back
Top