Hide and show picture based on the value with vlookup

[jc83ph]

Hello,

I need your help guys. I need to fix this code with formula(vlookup). when I enter the value in column A automatic the display the code in column B then the picture appears in column E.

But the problem is when I'm using the formula (vlookup) then display the serial based on the code but the picture not appear.
see below image.

Please note: there 2 sheets (sheet1 and sheet2). the sheet2 main source or main file.

here the code:

Private Sub Worksheet_Change(ByVal Target As Range)
     
If Target.Count <> "1" Or Target.Column <> 2 Or Target.Value = "" Then Exit Sub
    Dim wbpath      As String
    Dim photoPath   As String
    Dim wB          As Workbook
    Dim wS          As Worksheet
    Dim wS2          As Worksheet
    Dim photoName   As String
    Dim photoFile   As String
    Dim Cell        As Range
    Dim rng         As Range
    Dim sh          As Shape

   
    Dim noPhoto     As String
    noPhoto = "NOPHOTO.jpg"
   
    Dim photoExt    As String
    photoExt = ".jpg"
   
    'Turn screen updating off. You won't see the client file being updated.
    Application.ScreenUpdating = True
    Set wB = ActiveWorkbook
    Set wS = wB.Worksheets("Sheet1")

   
   
   ' path to your folder
   
    wbpath = "/sample/PICTURE/" & Application.PathSeparator

    photoPath = "/sample/PICTURE/" & Application.PathSeparator
   
    Set Cell = Target
    If Not Cell.Column = 2 Or Len(Trim(Cell.Value)) = 0 Then Exit Sub
    photoName = Cell.Value
    Set rng = wS.Range("E" & Cell.Row)
    photoFile = photoName & photoExt
    GoSub placePhotoInSheet
    Err.Clear
    On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub
   
deleteAllShapes:
For Each sh In wS.Shapes
    sh.Delete
Next sh
Return
   
placePhotoInSheet:
On Error Resume Next
wS.Shapes(photoName).Select
If Err.Number = 1 Then
    wS.Shapes(photoName).Visible = msoTrue
    Return
End If
GoSub deleteAllShapes
rng.Select
If Not Dir(photoPath & photoFile) = "" Then
    ActiveSheet.Pictures.Insert(photoPath & photoFile).Select
ElseIf Not Dir(wbpath & noPhoto) = "" Then
    ActiveSheet.Pictures.Insert(wbpath & noPhoto).Select
ElseIf Not Dir(photoPath & noPhoto) = "" Then
    ActiveSheet.Pictures.Insert(photoPath & noPhoto).Select
Else
    Return
End If
With Selection.ShapeRange
    .Name = photoName
    .LockAspectRatio = msoTrue
    .Top = rng.Top
    .Left = rng.Left
    '.Width = 141.75
    .Height = 500
    .IncrementLeft 0.75
    .IncrementTop -510
End With
rng.Offset(1, -rng.Column + 2).Select
Return

End Sub

 

[DanteAmor]

Hi, welcome to the board.

I see some problems in your path folders.
Are the images in any network?
Or are they in a drive?
- In the next line the drive is missing eg. (C : )
- You have Forward slash (/) and it should be Backslash (\)
- In the end you are adding another Backslash (Application.PathSeparator: \)

wbpath = "/sample/PICTURE/" & Application.PathSeparator

It should be something like this:
wbpath = "C:\sample\PICTURE\"

Review and comment

 

[jc83ph]

Hi, welcome to the board.

I see some problems in your path folders.
Are the images in any network?
Or are they in a drive?
- In the next line the drive is missing eg. (C : )
- You have Forward slash (/) and it should be Backslash (\)
- In the end you are adding another Backslash (Application.PathSeparator: \)

wbpath = "/sample/PICTURE/" & Application.PathSeparator

It should be something like this:
wbpath = "C:\sample\PICTURE\"

Review and comment

Hi .

I'm using mac.

 

[Fluff]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply links to any other sites you have asked this question.

 

[jc83ph]

Hi, welcome to the board.

I see some problems in your path folders.
Are the images in any network?
Or are they in a drive?
- In the next line the drive is missing eg. (C : )
- You have Forward slash (/) and it should be Backslash (\)
- In the end you are adding another Backslash (Application.PathSeparator: \)

wbpath = "/sample/PICTURE/" & Application.PathSeparator

It should be something like this:
wbpath = "C:\sample\PICTURE\"

Review and comment

Hi dante,

I need only to fix the problem.

the problem i'm using the formula (Vlookup) but the picture not appear. without formula the picture is appear.

I want to appear the picture using the vlookup. Please I need your help. Thanks.

 

[DanteAmor]

As fluff says, you should put the links where you asked the same question.

I don't have Mac, I can't try.

In your macro you have this:

Target.Column <> 2 Or Target.Value = "" Then Exit Sub

That means, if you modify a different column to column 2 then exit sub.

The event is activated if you modify a data in column B.
If in column B you have a formula, the data in the cell is not modified, the formula remains the same, the result is the one that changes, but column B is not changing.
You must modify your line to this:

Target.Column <> 1 Or Target.Value = "" Then Exit Sub

Then each time you change the data in column A, the event is activated.

 

[jc83ph]

As fluff says, you should put the links where you asked the same question.

I don't have Mac, I can't try.

In your macro you have this:

Target.Column <> 2 Or Target.Value = "" Then Exit Sub

That means, if you modify a different column to column 2 then exit sub.

The event is activated if you modify a data in column B.
If in column B you have a formula, the data in the cell is not modified, the formula remains the same, the result is the one that changes, but column B is not changing.
You must modify your line to this:

Target.Column <> 1 Or Target.Value = "" Then Exit Sub

Then each time you change the data in column A, the event is activated.

As fluff says, you should put the links where you asked the same question.

I don't have Mac, I can't try.

In your macro you have this:

Target.Column <> 2 Or Target.Value = "" Then Exit Sub

That means, if you modify a different column to column 2 then exit sub.

The event is activated if you modify a data in column B.
If in column B you have a formula, the data in the cell is not modified, the formula remains the same, the result is the one that changes, but column B is not changing.
You must modify your line to this:

Target.Column <> 1 Or Target.Value = "" Then Exit Sub

Then each time you change the data in column A, the event is activated.

Hi Dante,
the code working also in window the different only is slash in window like \ in mac is /.

the column A is code only and column is serial mean this the name of picture.

there no option to show the picture if i use the formula?

 

[DanteAmor]

Try this

Target.Column <> 1

Also:

Set Cell = Target.offset(, 1)

Delete this line

If Not Cell.Column = 2 Or Len(Trim(Cell.Value)) = 0 Then Exit Sub

 

[Fluff]

@jc83ph
Please do not ignore moderator instructions.
You must supply links to any other sites where you have asked this question.

 

[jc83ph]

Hi Dante,
the code working also in window the different only is slash in window like \ in mac is /.

the column A is code only and column is serial mean this the name of picture.

there no option to show the picture if i use the formula?

Hello date,


Finally is work. thanks