CODEWITHSUNDEEP
excelvba
BecaB
BecaB

Reputation: 11

Deleting images but can't avoid deleting command button

I'm not a regular user of vba, so when I started doing this code I was totally blank. I already deleted all the images on the worksheet, including command buttons and a logo I didn't meant to delete.

This is my code 

Private Sub CommandButton2_Click()

Dim Pic As Object

Range("D20:D3000").ClearContents

For Each Pic In ActiveSheet.Pictures
Pic.Delete
Next Pic


End Sub

As you can see I need it to delete the images on the D column, but it deletes the images of the whole sheet. I can't find a way to exclude command buttons (I'm using 2 on that sheet) of the deleting instruccion. Would some of you please, please, please help me? I'm a mess right now.

Upvotes: 1

Views: 865

Answers (2)

Étienne Laneville
Étienne Laneville

Reputation: 5031

The following compares the picture's range with your range and deletes it if they intersect:

Private Sub CommandButton2_Click()
    Dim objPicture As Object
    Dim objPictureRange As Object
    Dim objRange As Object

    Set objRange = Range("D20:D3000")
    objRange.ClearContents

    For Each objPicture In ActiveSheet.Pictures

        ' Get Picture's range
        Set objPictureRange = Range(objPicture.TopLeftCell.Address & ":" & objPicture.BottomRightCell.Address)

        ' Check if Picture is in Range
        If Not Intersect(objRange, objPictureRange) Is Nothing Then
            ' Delete picture
            objPicture.Delete
        End If

    Next Pic

End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

I need it to delete the images on the D column, but it deletes the images of the whole sheet.

You are iterating all pictures on the sheet, and never verify where on the sheet the picture is before you invoke its Delete method.

Interestingly, if you have one picture and one ActiveX command button on Sheet1, then Sheet1.Pictures.Count returns, unexpectedly, 2 - that's why the loop is also deleting command buttons.

I'd suggest iterating the Shapes collection instead: a Shape object has an interface that makes it much easier to tell a picture apart from an ActiveX button, ...and to know where on the sheet it's at.

Using Shape.Type we can know if we're looking at a msoPicture, and with Shape.TopLeftCell we can get a Range object representing the top-left cell the picture is located at; if the Column of that Range is 4, then we're looking at a shape whose upper-left corner is located in column D. Thus:

Dim currentShape As Shape
For Each currentShape In ActiveSheet.Shapes
    If currentShape.Type = msoPicture And currentShape.TopLeftCell.Column = 4 Then
        currentShape.Delete
    End If
Next

Note how declaring an explicit type that isn't Object for our loop variable, we get compile-time validation, and autocomplete/intellisense for every single one of these member calls, whereas calls made against Object are all late-bound (i.e. compiles perfectly fine with typos, even if you have Option Explicit at the top of your module) and will only blow up at run-time if anything is wrong (e.g. error 438 if you try to invoke a member that doesn't exist).

Upvotes: 1

Related Questions