dta180

Hello all! I am having a problem with this picture insert code. It works great when you manually type in the picture name... but I am using a VLOOKUP to return the picture name from a control number. So, the first instance works... but then when I change the control number, and a new picture name appears in the cell (from VLOOKUP)... the picture doesn't change. I have to make the picture name call active and press enter for it to work..... how can I fix this? I want to just type the Control number, and everything recalcs... may just be a simple "Application.Volatile" issue.. but I don't know where to place it.... without it interfering.

Please help.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rng As Range
Dim rngProducts As Range
Dim pic As Picture, shp As Shape
Dim szInvalids As String

On Error Resume Next
'Only insert the picture if it's in the area where they type the Picture Names
'Change "C3:C25" to a range of cells where they'll be typing in Picture Names
Set rngProducts = Intersect(Me.Range("C3:C25"), Target)
On Error GoTo 0


If Not rngProducts Is Nothing Then 'They entered a picture name
'Loop through each cell they entered in
' in case they copied several picture names into several cells
For Each rng In rngProducts
'Remove the exisitng picture (shape) from the cell to the right
For Each shp In Me.Shapes
If shp.TopLeftCell.Address = rng.Offset(0, 1).Address Then shp.Delete
Next shp
'Insert the picture
On Error Resume Next
Set pic = ActiveSheet.Pictures.Insert("C:\Users\crt2\Desktop\Appeal\" & rng.Text & ".jpg")
On Error GoTo 0
If Not pic Is Nothing Then 'The picture exists
With pic
.Height = rng.Offset(0, 1).Height
.Width = rng.Offset(0, 1).Width
.Left = rng.Offset(0, 1).Left
.Top = rng.Offset(0, 1).Top
End With
Else 'Invalid entry, add it to the list of invalids szInvalids = szInvalids & rng.Address & ": " & rng.Text & vbLf
End If
Next rng


'Show them the invalid entries if there were any
If Len(szInvalids) Then
szInvalids = "The following were either invalid picture name entries 0r " & vbLf & "the image could not be found" & vbLf & vbLf & szInvalids
MsgBox szInvalids, vbExclamation
End If
End If
End Sub

dta180

So, through some reading... I have figured out that the "Private Sub Worksheet_Change(ByVal Target As Range)" is causing my problem....

I know I won't be able to figure this out on my own.... just learning this stuff, any and all help will be much appreciated!