none
ActiveSheet.Pictures.Delete also delete ComboBox - how to prevent? RRS feed

  • Question

  • I wrote a code to insert a new picture after deleting existing one. But the code also deletes a ComboBox that resides in the worksheet. What do I do wrong?

    The code is as follows:

    Sub Worksheet_Calculate()
        Dim Xrg As Range
        Set Xrg = Range("M13")
        If Not Intersect(Xrg, Range("M13")) Is Nothing Then
        Worksheets("LOW SIDE").Pictures.Delete
        InsertPictureInRange1 Cells(11, 17).Value, Range("I15:M30")
        End If
    End Sub
    ================================================
    Sub InsertPictureInRange1(PictureFileName As String, TargetCells As Range)
    ' inserts a picture General View and resizes it to fit the TargetCells range
    Dim p As Object, t As Double, l As Double, w As Double, h As Double
        If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
        If Dir(PictureFileName) = "" Then Exit Sub
        ' import picture
        Set p = Worksheets("LOW SIDE").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
    End Sub

    By the way, the Worksheet I am using the code in called "Low Side".


    • Edited by NivEldor Tuesday, December 19, 2017 2:54 PM
    Tuesday, December 19, 2017 2:51 PM

Answers

  • Hi Niv Eldor,

    I don't know what kind of objects/shapes are in your sheet.
    Please check Name and Type of your shapes, and delete pictures except ComboBox.
      
    ' ---[Calculate]
    Private Sub btn_Calculate_Click()
        Dim Xrg As Range
        Set Xrg = Range("M13")
        ' ---
        If Not (Intersect(Xrg, Range("M13")) Is Nothing) Then
            Dim shp As Shape
            Dim idx As Integer: idx = 11
            ' --- Delete Pictures 
            For Each shp In ActiveSheet.Shapes
                ' --- for debugging: display Type and Name of Shapes in column 1---
                ActiveSheet.Cells(idx, 1).Value = shp.Type & ", " & shp.Name
                idx = idx + 1
                ' --- delete except 12(ComboBox, Button, etc)
                If (shp.Type <> 12) Then
                    shp.Delete
                End If
            Next
            ' --- Insert Picture(1st-parameter: image file, 2nd-parameter:displaying area)
            InsertPictureInRange1 Cells(11, 17).Value, Range("I15:M30")
        End If
    End Sub
    Regards,

    Ashidacchi


    Thursday, December 21, 2017 3:10 AM
  • Hello Ashidacchi and Terry,

    Your suggestions really helped!!

    I found out that the object that I did not want to delete is Drop-down number 8, and the wmf file is number 11. 

    Every thing works now:

    This code works:

    Sub Worksheet_Calculate()
        Worksheets("Low Side").Unprotect
        Dim Xrg As Range
        Set Xrg = Range("M13")
        If Not Intersect(Xrg, Range("M13")) Is Nothing Then
        Dim shp As Shape
            For Each shp In Worksheets("LOW SIDE").Shapes
                If (shp.Type <> 8) Then  ' --- 8: DropDown (except Picture)
                    shp.Delete
                End If
            Next
        InsertPictureInRange1 Cells(11, 17).Value, Range("I15:M30")
        End If
        Worksheets("Low Side").Protect
    End Sub

    I have another question:

    The code works, but it activates for any change in the worksheet, not only after "M13" is changed. For example, if one of the Drop-down selection is changed, the code runs again. What shall I use instead of Worksheet_Calculate ?

    Thanks,

    Niv Eldor


    Niv Eldor

    Thursday, December 21, 2017 1:54 PM

All replies

  • Hi NivEldor,

    You need to delete objects only when they are Pictures.
    Sub Worksheet_Calculate()
        Dim Xrg As Range
        Set Xrg = Range("M13")
        If Not (Intersect(Xrg, Range("M13")) Is Nothing) Then
            ' --- check if shape is picture ---
    Dim shp As Shape For Each shp In Worksheets("LOW SIDE").Shapes If (shp.Type <> 12) Then ' --- 12: ComboBox, Button, etc (except Picture) shp.Delete End If Next ' --- do not use --- Worksheets("LOW SIDE").Pictures.Delete InsertPictureInRange1 Cells(11, 17).Value, Range("I15:M30") End If End Sub


    Regards,

    Ashidacchi



    • Edited by Ashidacchi Wednesday, December 20, 2017 12:04 AM
    • Proposed as answer by Terry Xu - MSFT Wednesday, December 20, 2017 3:02 AM
    Tuesday, December 19, 2017 11:52 PM
  • Ashidacchi - Thanks, but... I changed the code and now it delete everything rather only one ComboBox. 

    Now, after changing the code per your suggestion, the new picture is inserted, but all other ComboBoxes in the worksheet disappear.

    Maybe I need to tell you that the pictures I am inserting are with extension .wmf.

    Also, maybe the "12" ('-----12: ComboBox, Button, etc. (except picture)) is not recognized as a definition of  everything but a Picture.

    Please help.


    Niv Eldor

    Wednesday, December 20, 2017 1:47 PM
  • Hello NivEldor,

    You could try to observe the type for each shape when looping all the shapes. You could find the shape type of the picture you inserted.

    In my test, the picture type for .wmf file is msoLinkedPicture(11). So, I would suggest you use below code to just deleted the picture you inserted. 

     For Each shp In Worksheets("LOW SIDE").Shapes
                If (shp.Type = msoLinkedPicture) Then
                    shp.Delete
                End If
            Next

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 21, 2017 1:43 AM
  • Hi Niv Eldor,

    I don't know what kind of objects/shapes are in your sheet.
    Please check Name and Type of your shapes, and delete pictures except ComboBox.
      
    ' ---[Calculate]
    Private Sub btn_Calculate_Click()
        Dim Xrg As Range
        Set Xrg = Range("M13")
        ' ---
        If Not (Intersect(Xrg, Range("M13")) Is Nothing) Then
            Dim shp As Shape
            Dim idx As Integer: idx = 11
            ' --- Delete Pictures 
            For Each shp In ActiveSheet.Shapes
                ' --- for debugging: display Type and Name of Shapes in column 1---
                ActiveSheet.Cells(idx, 1).Value = shp.Type & ", " & shp.Name
                idx = idx + 1
                ' --- delete except 12(ComboBox, Button, etc)
                If (shp.Type <> 12) Then
                    shp.Delete
                End If
            Next
            ' --- Insert Picture(1st-parameter: image file, 2nd-parameter:displaying area)
            InsertPictureInRange1 Cells(11, 17).Value, Range("I15:M30")
        End If
    End Sub
    Regards,

    Ashidacchi


    Thursday, December 21, 2017 3:10 AM
  • Hello Ashidacchi and Terry,

    Your suggestions really helped!!

    I found out that the object that I did not want to delete is Drop-down number 8, and the wmf file is number 11. 

    Every thing works now:

    This code works:

    Sub Worksheet_Calculate()
        Worksheets("Low Side").Unprotect
        Dim Xrg As Range
        Set Xrg = Range("M13")
        If Not Intersect(Xrg, Range("M13")) Is Nothing Then
        Dim shp As Shape
            For Each shp In Worksheets("LOW SIDE").Shapes
                If (shp.Type <> 8) Then  ' --- 8: DropDown (except Picture)
                    shp.Delete
                End If
            Next
        InsertPictureInRange1 Cells(11, 17).Value, Range("I15:M30")
        End If
        Worksheets("Low Side").Protect
    End Sub

    I have another question:

    The code works, but it activates for any change in the worksheet, not only after "M13" is changed. For example, if one of the Drop-down selection is changed, the code runs again. What shall I use instead of Worksheet_Calculate ?

    Thanks,

    Niv Eldor


    Niv Eldor

    Thursday, December 21, 2017 1:54 PM
  • Hi Niv Eldor,

    I hope you will create a new thread if your first issue is resolved.

    I'm wondering where and how Sub Worksheet_Calculate() is called.
    Please share your file via cloud storage such as OneDrive, Dropbox, and so on.
    (Remember to edit/modify your private/important data, before sharing)

    Regards,

    Ashidacchi

    P.S.
    Usually "Intersect" is used like this:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not (Intersect(Target, Range("M13")) Is Nothing) Then
            MsgBox "value of cell [M13] has been changed."
        End If
    End Sub
    • Edited by Ashidacchi Friday, December 22, 2017 1:22 AM
    Friday, December 22, 2017 1:17 AM