insert picture in worksheet

[bastian18]

Hello.
I am trying to run a code that after reading the value of cell "A:2" must display a picture in the active worksheet.


This is my code:

Public Sub insertpicture()
If Intersect(Target, Range("A2")) Is Nothing Then
Exit Sub
End If
Dim myPict As Picture
With Range("A20:J20")
'Set myPict = Range("A20:J20").Parent.Pictures.InsertCurrentProject.Path\BDR\Pictures\ & Target.Value & ".jpg")
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With
End Sub

However, by the moment I am facing to problems when running the code :
1. the code vba displays "Error 424 Object Required" for line: "If Intersect(Target, Range("A2")) Is Nothing Then"
2. VBA coloured in red my relative path in line:Set myPict = Range("AI7:J17").Parent.Pictures.InsertCurrentProject.Path\BDR\Pictures\ & Target.Value & ".jpg")


So, any help would be greatly appreciated.

 

[PatOBrien198]

First, "Target" does not seem to be defined.
The Intersect function is expecting a range object named "Target"


This is common in the Workbook_SheetChange event code

I got the following code to work.... changing the PicPath

Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("A2")) Is Nothing Then
 Exit Sub
End If


Dim MyPict As Picture
Dim rng As Range
Dim PicPath As String


PicPath = "C:\Users\me\Pictures\" & Target.Value & ".jpg"

If Dir(PicPath) = "" then
 msgbox "Picture file not found!"
 exit Sub
End If

Set MyPict = ActiveSheet.Pictures.Insert(PicPath)

Set rng = Range("a17:c25")
With rng
    MyPict.Top = .Top
    MyPict.Width = .Width
    MyPict.Height = .Height
    MyPict.Left = .Left
End With

End Sub

 

[bastian18]

PatObroen198.
I really appreciate your reply.
When I copy and paste the code VBA displays in red the following line of code

Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 

[PatOBrien198]

PatObroen198.
I really appreciate your reply.
When I copy and paste the code VBA displays in red the following line of code

Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

sorry.... there's an extra "Sub"
just copy the code between Sub ... and End Sub into the workbook_SheetChange event.
s