runtime error “out of memory”+excel macro

hi experts. i have one macro,which i called when cell change. this macro select images and delete them and insert another image depending on cell value using following code. i have same code for two sheets.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Shapes.SelectAll
      Selection.Delete

'insert image code here. End Sub

in one sheet its working perfectly fine and delete all images while in other sheet , it give me runtime error "out of memory" and highlight following line.

 ActiveSheet.Shapes.SelectAll

can any one tell me why this is happening? it works perfectly fine in one and not in other.

one other thing i want to tell you is. it was working fine when i gave this macro excel to my client,both sheets were working fine, suddenly after 2 days, he started getting error on one sheet on which he was working lot.

dont know why this is happening. can anyone tell me whats reason and how to solved it?

Originally Posted by romperstomper

Does he have an autofilter applied to the second sheet (or has he had one)?

means? i didnt get it sir. can you explain in bit more detail?

Originally Posted by romperstomper

I mean, has he applied a filter to the second sheet (where the code fails)? If so, your code will be trying to select the filter dropdowns, which you can't do. You need to loop through the shapes, check if each one is a picture and then delete it if so.

ok got it, i will ask him and let you know.
right now i have change selectall to this.

ActiveSheet.Shapes.Item(1).Select

my sheet will always have one image, will this work??

Originally Posted by romperstomper

Not necessarily. If it's a picture rather than an image control, you could use:

ActiveSheet.Pictures(1).Delete

instead.

i am not expert in excel macro.

following is code which add image to excel. so looking at it it seems its picture.
right?

  If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    If Dir(PictureFileName) = "" Then Exit Sub
    ' import picture
   
    Set p = ActiveSheet.Pictures.Insert(PictureFileName)
    ' determine positions
    With TargetCells
        t = .Top
        l = .Left
        w = .Offset(0, .Columns.Count).Left - .Left
        h = .Offset(.Rows.Count, 0).Top - .Top
    End With
    ' position picture
    With p
        .Top = t
        .Left = l
        .Width = w
        .Height = h
    End With
    Set p = Nothing