Code to delete some images

[dpaton05]

I have this code to delete some images on my spreadsheet, but there are now 2 images I don't want the code to delete. The names of them are Image1 and Image2.
How can I change it so that it won't delete those images?

Private Sub cmdNoSig_Click()
ActiveSheet.Unprotect Password:="npssadmin"
Dim Pic As Object
     For Each Pic In ActiveSheet.Pictures
        If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then
            Pic.Delete
        End If
    Next Pic
'ActiveSheet.Protect Password:="npssadmin"
End Sub

Thanks,
Dave

 

[Michael M]

Maybe something like

Private Sub cmdNoSig_Click()
ActiveSheet.Unprotect Password:="npssadmin"
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
    If Pic.Name <> "Image1" And Pic.Name <> "Image2" Then
          If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then
              Pic.Delete
          End If
    End If
Next Pic
'ActiveSheet.Protect Password:="npssadmin"
End Sub

 

[dpaton05]

Maybe something like

Private Sub cmdNoSig_Click()
ActiveSheet.Unprotect Password:="npssadmin"
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
    If Pic.Name <> "Image1" And Pic.Name <> "Image2" Then
          If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then
              Pic.Delete
          End If
    End If
Next Pic
'ActiveSheet.Protect Password:="npssadmin"
End Sub

That didn't work Michael but it gave me the format of the code to work with.

This is the code that I got it to work with

Private Sub cmdNoSig_Click()
ActiveSheet.Unprotect Password:="npssadmin"
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
    If Pic.Name = "Image1" Or Pic.Name = "Image2" Then
          'If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then
              Pic.Delete
          'End If
    End If
Next Pic
'ActiveSheet.Protect Password:="npssadmin"
End Sub

I want code to paste the images in as "ImgG" or "ImgJ", not image1 or image2. I will then update the delete code.


What code do I use?

 

[Michael M]

The code you posted uses an OR statement, whereas mine used an AND statement.

If Pic.Name = "Image1" Or Pic.Name = "Image2" Then

Make sure you use the right one, there is a big difference !!

I want code to paste the images in as "ImgG" or "ImgJ", not image1 or image2

So why not simply rename them

If Pic.Name = "Image1" then pic.name = "ImgG"

 

[dpaton05]

Is there code to delete only images on the sheet as my command buttons are being deleted now?

 

[Michael M]

Yes, using the naming convention for them as well

 If Pic.Name <> "CmdButton1" And Pic.Name <> "CmdButton2" Then

 

[dpaton05]

Thanks Michael, I got it working with this code.

Private Sub cmdNoSig_Click()
ActiveSheet.Unprotect Password:="npssadmin"
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
    If Pic.Name <> "lblActivities" And Pic.Name <> "lblNotes" And Pic.Name <> "cmdAddRow" And Pic.Name <> "cmdDeleteRow" And _
    Pic.Name <> "cmdDelSelect" And Pic.Name <> "cmdG" And Pic.Name <> "cmdJ" And Pic.Name <> "cmdNoSig" And Pic.Name <> "cmdSend" And _
    Pic.Name <> "cmdDeleteQuoteLines" And Pic.Name <> "imgLogo" Then
         ' If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then
              Pic.Delete
          'End If
    End If
Next Pic
'ActiveSheet.Protect Password:="npssadmin"
End Sub

What does this line of code do that I had commented out?

' If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then

 

[Michael M]

If you hadn't commented it out....any pics with their TopLeft outside the range wouldn't be affected !!

 

[dpaton05]

' If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then

That commented line of code does not make sense. Is it a double negative? Could you describe it to me in words please?

 

[Michael M]

It's probably easier to read it AND try an example....See here

https://www.ozgrid.com/VBA/vba-intersect.htm