Size Pictures in an Excel Worksheet to the Cell Size Using VBA

Question

Using VBA I am needing to resize pictures in a range on an Excel worksheet to the actual size of each cell it is in. In other words, I want uniformity in my picture sizes and to be able to sort them within a worksheet. I don't want to change my cell size to fit the picture. I saw something close to this in stackoverflow, but it was for a table, not a worksheet with rows. I already have coding for everything I need except to change an image size to the cell size for each picture in a range of cells. Does anyone have any VBA coding for this?

Provide the specific source code (or isolated sample) for more details, as well as the result you are getting and the expected result. This will help to get more response from the community. — David Leal

Excelosaurus Excelosaurus · Accepted Answer · 2020-07-20T03:31:01

Assuming each shape's top left corner is already within its destination cell, you could scan all shapes on a worksheet and set each shape's Left, Top, Width and Height properties to its containing cell's:

Option Explicit

Public Sub AdjustShapesToTheirCells(ByVal poWorksheet As Excel.Worksheet)
    Dim shp As Excel.Shape
    
    For Each shp In poWorksheet.Shapes
        shp.LockAspectRatio = msoFalse
        shp.Left = shp.TopLeftCell.Left
        shp.Top = shp.TopLeftCell.Top
        shp.Width = shp.TopLeftCell.Width
        shp.Height = shp.TopLeftCell.Height
    Next
End Sub

You could then call the sub like so:

AdjustShapesToTheirCells Sheet1

Size Pictures in an Excel Worksheet to the Cell Size Using VBA

1 Answers