Code to insert .jpg from folder based on changing formula result in cell & deleting only inserted image when formula change and not ActiveX controls.

[DeonRoux]

Hi, humbly requesting assistance.

1. I have the code below which inserts a picture from a folder D:\CLLT\CLLT Images into "K4" based on the picture name in "P5". It works perfectly fine if I change "P5" manually.

2. How do I change the code if "P5" is an "if formula" which generates the filename and changes with other cell inputs to insert the picture from my folder whenever the formula in "P5" changes?

3. Also, how do I modify the code to delete only the .jpg pictures and not my ActiveX control boxes? (to delete pic every time the selection changes)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPict As Picture
Dim PictureLoc As String

If Target.Address = Range("P5").Address Then

ActiveSheet.Pictures.Delete

PictureLoc = "D:\CLLT\CLLT Images\" & Range("P5").Value & ".jpg"

With Range("K4")
Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)
.RowHeight = 14.5
myPict.Top = .Top
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
myPict.Height = 160
End With

End If

End Sub

 

[tonyyy]

DeonRoux,

Welcome to the Board.

You might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPict As Picture, shp As Shape
Dim PictureLoc As String
Dim rng As Range
Set rng = Union(Range("Q1"), Range("Q2"), Range("R15")) 'Change ranges to match inputs to P5 formula

If Not Intersect(Target, rng) Is Nothing Then
    For Each shp In ActiveSheet.Shapes
        If shp.Top = Range("K4").Top And shp.Left = Range("K4").Left Then shp.Delete
        Exit For
    Next shp
    PictureLoc = "D:\CLLT\CLLT Images\" & Range("P5").Value & ".jpg"
    With Range("K4")
        Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)
        .RowHeight = 14.5
        myPict.Top = .Top
        myPict.Left = .Left
        myPict.Placement = xlMoveAndSize
        myPict.Height = 160
    End With
End If
End Sub

Rather than P5 as the Target range, use the input ranges to the P5 formula. And rather than Pictures.Delete, loop through the pictures/shapes and delete only the one in K4.

Cheers,

tonyyy

 

[DeonRoux]

DeonRoux,

Welcome to the Board.

You might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myPict As Picture, shp As Shape
Dim PictureLoc As String
Dim rng As Range
Set rng = Union(Range("Q1"), Range("Q2"), Range("R15")) 'Change ranges to match inputs to P5 formula

If Not Intersect(Target, rng) Is Nothing Then
    For Each shp In ActiveSheet.Shapes
        If shp.Top = Range("K4").Top And shp.Left = Range("K4").Left Then shp.Delete
        Exit For
    Next shp
    PictureLoc = "D:\CLLT\CLLT Images\" & Range("P5").Value & ".jpg"
    With Range("K4")
        Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)
        .RowHeight = 14.5
        myPict.Top = .Top
        myPict.Left = .Left
        myPict.Placement = xlMoveAndSize
        myPict.Height = 160
    End With
End If
End Sub

Rather than P5 as the Target range, use the input ranges to the P5 formula. And rather than Pictures.Delete, loop through the pictures/shapes and delete only the one in K4.

Cheers,

tonyyy

Thank you Tony,

There are various inputs to my "P5" i.e:
"=IF(B5="Controls";INDEX(Controls!M:M;MATCH(Dashboard!C5;Controls!F:F));IF(B5="Signs";INDEX(Signs!M:M;MATCH(Dashboard!C5;Signs!F:F));IF(B5="Rules";INDEX(Rules!M:M;MATCH(Dashboard!C5;Rules!F:F)))))"

Struggling with the Target Range; not working (maybe I'm a bit stupid; pls dont confirm ).
Are there no way to just link the output of "P5" formula i.e. the file name to be used in the PictureLoc = "D:\CLLT\CLLT Images" & Range("P5").Value & ".jpg"as it changes?

regards

Deon