Results 1 to 3 of 3

Thread: Keep formatted shape to use in other workbooks

  1. #1
    Seeker cricket1001's Avatar
    Join Date
    Aug 2017
    Lady Lake, FL
    Excel Version

    Keep formatted shape to use in other workbooks

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.


  2. #2

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Excel Version
    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:
    Click image for larger version. 

Name:	2019-12-14_235542.jpg 
Views:	6 
Size:	13.4 KB 
ID:	9493
    I got this:
    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:
    ActiveSheet.Shapes.AddShape(msoShapeRightArrow, 96, 45, 77.04, 38.16).Select
    to like:
    ActiveSheet.Shapes.AddShape(msoShapeRightArrow, ActiveCell.Left, ActiveCell.Top, 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 by p45cal; 2019-12-15 at 12:05 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts