by Allen Wyatt
(last updated July 22, 2017)
John often pastes screen shots into an Excel workbook. He invariably needs to crop these graphics on all four sides by a set amount. Because he does this task so often, he wonders if there is a way to do it easily in a macro.
There are a number of ways you can address this problem, and all of them involve the use of macros. (There are no built-in commands to do such a task.) If you simply want to crop a graphic you've already added, you can do so by using a simple macro such as this:
Sub CropPicture() With Selection.ShapeRange.PictureFormat .CropLeft = 200 .CropTop = 300 .CropBottom = 50 .CropRight = 100 End With End Sub
You would, of course, need to specify the values for the cropping. These values are specified in points, and they are relative to the original size of the picture. So, for instance, if you previously scaled the selected picture to 150% of its original size, and then you run this macro, all of the crop settings will appear to be 1.5 times their actual values. (So, for instance, the left cropping would appear to be 300 pixels, not 200 pixels.)
The macro could easily be modified so that it does the pasting and then automatically crops the image. This is done by simply adding a single-line paste command:
Sub PasteCropPicture() ActiveSheet.Paste With Selection.ShapeRange.PictureFormat .CropLeft = 200 .CropTop = 300 .CropBottom = 50 .CropRight = 100 End With End Sub
The macro assumes that you previously copied the screen shot to the Clipboard; if you try to run it without having done so, you may well get an error.
There is one other thing to remember when running a macro such as this: When it is done, it will appear that your image has moved from its original position in the worksheet. This occurs because cropping from all four sides will change the apparent upper-left corner of the image to a different location. You can, if desired, compensate for this in your macro by adding code that "remembers" the upper-left corner of the image:
Sub CropPositionPicture() Dim MyShape As Shape Dim LeftSide As Single Dim TopSide As Single Set MyShape = Selection.ShapeRange LeftSide = MyShape.Left TopSide = MyShape.Top With MyShape.PictureFormat .CropLeft = 200 .CropTop = 300 .CropBottom = 50 .CropRight = 100 End With MyShape.Left = LeftSide MyShape.Top = TopSide End Sub
Of course, there is one thing to remember when you choose to crop graphics, regardless of how you choose to do so. Cropping doesn't affect image size. So, for instance, if you crop a screen shot so that only half of the screen is visible, all the "invisible" portions of the screen shot are still there, taking up space. This adds to the overall size of your workbook, sometimes amazingly so.
The only way around this is to not crop any graphics within Excel. Instead, process the image in a graphics program, save your processed image, and then insert that processed image in the worksheet. The result is smaller workbooks which means faster saving and loading.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11580) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When you change from one worksheet to another, you may want to have Excel automatically run a macro for the worksheet you ...Discover More
Need to know if a particular cell is clicked with the mouse? Excel has no particular event handler for clicking in this ...Discover More
If you create a user form in VBA that includes checkboxes, you may want to make the checkboxes larger. You can't adjust ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.