Excel VBA crop picture/shape and put in cells

Question

I am processing five pictures (printscreen shape) and would like to place them in selected cells, eg. cells N21, P21, S21, U21 & W21. I have the VBA code below to crop the five pictures/shapes and it is working fine, but I am not sure how to place them in the selected cells above. Can anyone enlighten me? Also, how to delete five pictures after that with a VBA button?

Sub CropPictures()
    Application.ScreenUpdating = False

    Dim shp As Shape 
    Dim sngMemoLeft As Single
    Dim sngMemoTop As Single
    Dim i As Integer, j As Integer

   i = 0: j = 0
   For Each shp In ActiveSheet.Shapes
       i = i + 1

       With shp
           If .Type = 13 Or .Type = 15 Then
               j = j + 1
               sngMemoLeft = .Left
               sngMemoTop = .Top
               With .PictureFormat
                   .CropLeft = 20
                   .CropTop = 195
                   .CropBottom = 27
                   .CropRight = 565
               End With
               With shp
                   .Height = 20
                   .Width = 195
               End With
               .Left = sngMemoLeft
               .Top = sngMemoTop
           End If
        End With
    Next shp
End Sub

You are already moving the pictures with `.Left` and `.Top`. If you want them to be inside the cells, that [does not happen](https://stackoverflow.com/a/8684954/11683). — GSerg, Oct 02 '17 at 07:18
hi thank you for sharing, ya now the crop picture go inside that cells i select, can i refine a cell ranges and put them one by one? — robin, Oct 02 '17 at 07:23
do not use `with shp` inside another `with shp` .... remove the inner `with shp` and outdent `.height` and `.width` to be same as `.left` — jsotola, Oct 02 '17 at 07:27

QHarr · Answer 1 · 2017-10-02T07:48:14.643

You can position as shown in the following post pic into excel at specific cell This works by setting the top left property of your shape to a cell reference.

Assuming you want to delete all shapes in the active sheet you would put something along the lines of the following:

Dim shp As Excel.shape

For Each shp In ActiveSheet.Shapes
    shp.Delete
Next

If a specific 5 shapes you can use the same structure but test if the shape has a certain name and then delete.

If shp.Name = "Shape1" Then 
        shp.Delete 
End If

I also prefer to explicitly name my pictures e.g. myPict.Name = "Pic1" this avoids later problems with tracking shape numbers.

thank you so much for pointing me to right direction, will relook the code. — robin, Oct 02 '17 at 08:05

Excel VBA crop picture/shape and put in cells

1 Answers1