CODEWITHSUNDEEP
excelvba
David
David

Reputation: 1293

Excel - Deleting images when deleting a row

I have a macro that imports images from a directory and places them in Excel cells that are made just big enough to fit the image in.

A snippet of the macro is below:-

'Set the Row Height and Column Width of the thumbnail

Range("A" & CStr(currRow)).RowHeight = ThumbnailSizeRef + 2 

Columns("A").ColumnWidth = (ThumbnailSizeRef - 5) / 5 'Column Width uses a font width setting, this is the formula to convert to pixels

'Add the thumbnail
Set sShape = ActiveSheet.Shapes.AddPicture(Filename:=sFilename, LinktoFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=0, Top:=0, Width:=ThumbnailSizeRef, Height:=ThumbnailSizeRef)

'Set the Left and Top position of the Shape
sShape.Left = Range("A" & CStr(currRow)).Left + ((Range("A" & CStr(currRow)).Width - sShape.Width) / 2)

sShape.Top = Range("A" & CStr(currRow)).Top + ((Range("A" & CStr(currRow)).Height - sShape.Height) / 2)

This all works fine. The images display correctly just in the cell as required. I can sort the cells successfully also and the images get moved correctly.

The problem I have is when I delete an entire row (right click on the row and delete), in this situation the image from the row I'm deleting jumps down and hides behind the image on the next row.

Is there any way that when I delete the row the image gets deleted as well?

Upvotes: 3

Views: 12749

Answers (3)

JMax
JMax

Reputation: 26591

You can change your picture properties to "Move and size with cells". Hence, when you delete your row, your image will be deleted too. Tested in Excel 2007.

Another solution is to add a comment and fill the picture in the background (see more info here : http://www.excelforum.com/excel-general/569566-embed-image-in-cell.html)

Upvotes: 4

user688334
user688334

Reputation:

There may be a better way but I can think of 2 workarounds.

  1. When you import the shape to a cell, specifically name the shape with a naming convention to identify the row/column (eg .Name = "ImageX-RowY-ColumnZ"). Then use a worksheet change event and this link Capture Deleted Rows to cycle through the shapes and delete the required shapes based on what's been deleted.

  2. Alternatively, fill a comment box with your image and when the row is deleted the comment & image also disappear

Eg 

 Sub test()
 ThumbnailSizeRef = 100
 currRow = 5
 sFilename = "C:\Users\....\Desktop\Untitled.png"

 Range("A" & CStr(currRow)).RowHeight = ThumbnailSizeRef + 2

 Columns("A").ColumnWidth = (ThumbnailSizeRef - 5) / 5

 With Sheet1
    With .Range("A" & currRow)
        .ClearComments
        .AddComment

        With .Comment
        .Visible = True
        .Text Text:=""
        .Shape.Left = Sheet1.Range("A" & currRow).Left
        .Shape.Top = Sheet1.Range("A" & currRow).Top
        .Shape.Width = Sheet1.Range("A" & currRow).Offset(0, 1).Left - Sheet1.Range("A" & currRow).Left
        .Shape.Height = Sheet1.Range("A" & currRow).Offset(1, 0).Top - Sheet1.Range("A" & currRow).Top
        .Shape.Fill.UserPicture sFilename
        .Shape.Line.ForeColor.RGB = RGB(255, 255, 255) 'hides connector arrow

        End With

    End With
End With

End Sub

Upvotes: 3

eggplant_parm
eggplant_parm

Reputation: 242

This isn't perfect, but it might meet your needs or at least get you going in the right direction.

Put this code in the worksheet module. When an event changes an entire row, it will delete the first shape it finds whose top left cell is in that row. This works if you're deleting a row, but it's also triggered if you cut a row, which is not desired. If you don't plan on cutting and pasting rows, than it's not an issue.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pic As Shape
    If Union(Target, Target.EntireRow).Address = Target.Address Then
        For Each pic In ActiveSheet.Shapes
            If pic.TopLeftCell.Row = Target.Row Then
                pic.Delete
                Exit For
            End If
        Next pic
    End If
End Sub

Upvotes: 1

Related Questions