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

Supposing there are multiple pictures in a sheet which are in different sizes. Do you have any ways to enlarge or shrink these pictures in one size with one click? There is no built-in feature to solve it, but in this article, I introduce a VBA code to enlarge or shrink all pictures in the sheet with one click.

Enlarge and shrink pictures with one click by VBA

Enlarge and shrink pictures with one click by VBA

Do as below steps to enlarge or shrink pictures with one click.

1. Right click at the sheet tab where you want to resize the pictures, and select View Code from context menu.

2. In the popping Microsoft Visual Basic for Applications window, and paste below code to the blank script.

VBA: Enlarge and shrink pictures with one click

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'UpdatebyEntendoffice20161111
    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

3. Save the code and close the window. And when you click at any cell of column A, all pictures will be shrunk. See screenshot:

4. And when you click at the cell which at the right top of the picture, the relative picture will be enlarged. See screenshot:

Tip.If you want to quickly insert watermark in a sheet, please try to use the Kutools for Excel’s Insert Watermark as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now.