VBA Macro: Resize and Center Images in Excel Based on Cell Location

This VBA macro resizes and repositions images in a worksheet based on their location in specific cells. The macro loops through all shapes in the active worksheet and checks if their top-left cell address matches any of the specified addresses. If a match is found, the shape is resized to a fixed height and width, and positioned at the center of its top-left cell. The 'LockAspectRatio' property is also set to False to allow the images to be resized proportionally. If an error occurs during the loop, it is ignored and the macro continues to the next shape.

Sub nnn()
Dim sh As Shape
On Error Resume Next
For Each sh In ActiveSheet.Shapes
sh.LockAspectRatio = msoFalse
If sh.TopLeftCell.Address = "$L$9" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
ElseIf sh.TopLeftCell.Address = "$L$10" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
ElseIf sh.TopLeftCell.Address = "$L$11" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
ElseIf sh.TopLeftCell.Address = "$L$12" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
ElseIf sh.TopLeftCell.Address = "$L$13" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
ElseIf sh.TopLeftCell.Address = "$L$14" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
ElseIf sh.TopLeftCell.Address = "$L$15" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
ElseIf sh.TopLeftCell.Address = "$L$16" Then
sh.Height = 87.874015748
sh.Width = 87.874015748
sh.Left = (sh.TopLeftCell.Width - sh.Width) / 2 + sh.TopLeftCell.Left '定义图片位置于所在单元格中间位置
sh.Top = (sh.TopLeftCell.Height - sh.Height) / 2 + sh.TopLeftCell.Top '定义图片位置于所在单元格中间位置
End If
Next
End Sub

This macro is designed to automatically adjust images in cells 'L9' to 'L16'. You can modify the cell addresses in the code to customize it for your specific needs.