VBA to Delete a picture before adding one to target cell

[DCGNM]

Hi All

I have had some help with another user on how to create a button whereby I could open a popup, select a picture, have it stretched to fix and centered in a cell.

However, i just noticed that if there was an existing picture there, it would not be deleted, and the new picture that i selected would just be added on top of the original picture. Can i ask for help to add in one more code to clear/delete the cell of any pictures first before adding the new picture?

---------------------------------------------------------------------------
Private Sub CommandButton6_Click()
Dim Pic As Object
Dim rng As Range: Set rng = Sheets("Cover Page").Range("C7").MergeArea

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Select an image file"
.Filters.Clear
.Filters.Add "JPG", "*.JPG"
.Filters.Add "Graphics Interchange Format", "*.GIF"
.Filters.Add "Portable Network Graphics", "*.PNG"
.Filters.Add "All Pictures", "*.*"


If .Show = -1 Then


Set Pic = Sheets("Cover Page").Pictures.Insert(.SelectedItems(1))
With rng
Pic.Top = .Top
Pic.Height = .Height
If Pic.Width > .Width Then Pic.Width = .Width
Pic.Left = .Left + 0.5 * (.Width - Pic.Width)
End With
End If
End With
End Sub

 

[Logit]

.
See if this works :

Private Sub CommandButton6_Click()
Dim Pic As Object
Dim rng As Range: Set rng = Sheets("Cover Page").Range("C7").MergeArea


Sheets("Cover Page").Range("C7").ClearContents


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Select an image file"

 

[DCGNM]

Hi Logit

I got the Error "We can't do that to a merged cell"

 

[DanteAmor]

With the following, the image is named as "picture", then the next time the image called "picture" is deleted, each time you execute the macro, it will erase the image "picture" and the new one will be called "picture"

Private Sub CommandButton6_Click()
Dim Pic As Object
Dim rng As Range: Set rng = Sheets("Cover Page").Range("C7").MergeArea


With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Select an image file"
    .Filters.Clear
    .Filters.Add "JPG", "*.JPG"
    .Filters.Add "Graphics Interchange Format", "*.GIF"
    .Filters.Add "Portable Network Graphics", "*.PNG"
    .Filters.Add "All Pictures", "*.*"
    
    
    If .Show = -1 Then
    
    
        Set Pic = Sheets("Cover Page").Pictures.Insert(.SelectedItems(1))
        With rng
[COLOR=#0000ff]            On Error Resume Next[/COLOR]
[COLOR=#0000ff]            Sheets("Cover Page").Pictures("picture").Delete[/COLOR]
[COLOR=#0000ff]            On Error GoTo 0[/COLOR]
[COLOR=#0000ff]            Pic.Name = "picture"[/COLOR]
            
            Pic.Top = .Top
            Pic.Height = .Height
            If Pic.Width > .Width Then Pic.Width = .Width
            Pic.Left = .Left + 0.5 * (.Width - Pic.Width)
        End With
    End If
End With
End Sub

 

[DCGNM]

Hi DanteAmor

Thank you so much. That works like a charm

 

[Logit]

.
Merge cells are nice for visual display of information but can be a frustration to no end.

Always best to avoid merged cells.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.