How to enlarge or shrink a picture with one click in Excel?

Excel 2019 enables users to incorporate not just numerical data but also images and graphics. Including pictures and other images is a terrific way to make your spreadsheets look more appealing. You can generate your own photos to add to a workbook or copy and paste images into a spreadsheet, much like you can in Microsoft Word. You can even create your own images to add.

Excel gives you the ability to import graphics, shapes, symbols, and even 3D images and photos available on the web. Let's say that there are several pictures on a sheet, and they range in scale from small to large. You want to enlarge or reduce the size of these photographs with just one click. There is no built-in function that can solve the problem.

In this tutorial you are going to learn about a VBA code to enlarge orshrink the image in excel.

Enlarge or Shrink a Picture Using VBA Code

Follow the steps given below to write the VBA code.

Step 1

We have the following image added in an Excel sheet.

Step 2

Open an Excel sheet and right click at the sheet tab where you wish to resize the images. Then Select View Code from the context menu that appears. Or you can press F11 to open the VBA window.

Step 3

The popping Microsoft Visual Basic for application window will be opened.

In the new window that has appeared for Microsoft Visual Basic for Applications, type the following code into the empty module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.ScreenUpdating = False
   Dim xRg As Range, sPic As Shape
      For Each sPic In ActiveSheet.Shapes
         If Target.Column > 1 Then
            Set xRg = Target.Offset(, -1)
            With sPic
               If TypeName(.OLEFormat.Object) = "Picture" Then
                  If .TopLeftCell.Address = xRg.Address Then
                     .Height = 350
                     .Width = 250 
                  End If
               End If
            End With
         ElseIf Target.Column = 1 Then
            With sPic
               If TypeName(.OLEFormat.Object) = "Picture" Then
                  .Height = 60
                  .Width = 60
               End If
            End With
         End If
      Next sPic
   Application.ScreenUpdating = True
End Sub

Refer the following image.

Then, save the code and close the VBA window.

Step 4

If you click any of the cells in column A, all of the images will shrink. See the following image.

If you click on the cell that is located at the upper right of the image, the image that corresponds to that cell will become larger. See the below given image.


In this tutorial, we explained in detail how you can enlarge or shrink a picture in Excel with just one click.

Updated on: 10-Sep-2022

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started