Edit a VB to Vlookup a picture

[CraigFord]

i have used the VB code in the link below

http://www.mcgimpsey.com/excel/lookuppics.html

Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("F1")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
    End Sub

the problem i have is the code makes all pictures on the sheet hidden, however there are some pictures on the skeet that need to be shown all of the time, is there a way i can stop some pics being hidden?

 

[Andrew Poulsom]

You need to put the names of the pictures you want to show in F1 and F5.

 

[CraigFord]

You need to put the names of the pictures you want to show in F1 and F5.

i dont understand

 

[CraigFord]

i got the code working perfectly in a blank workbook now i have added it to the workbook i want to use it wont work!!

am i able to upload the document for smebody to assist

x

 

[CraigFord]

x

please help

 

[Andrew Poulsom]

Enter Picture 4 in F1 and Picture 5 in F5.

 

[CraigFord]

Enter Picture 4 in F1 and Picture 5 in F5.

Really Sorry, i think i have confused the post by asking multiple questions,

i assume that your post relates to my problem witht the dissapearing images, im not too concerned about the pictures i want shown dissapearing (as i can get round this using the pricures in a header which works well), my bigger problem is that i have made this work in a blank workbook however once i have done the exact same thing in the purchase order i have created it only shows the text not the image.

 

[CraigFord]

ok to give an update

i have now fixed the hidden pictures and got the VB working

the last thing i need is for the bottom right of the image to be inline with the bottom right of the cell

currently the picture is alligned with the top left

i assume this has something to do with the highlightd element of code below

Option Explicit

    Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = True
        With Range("G51")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    [COLOR=Red]oPic.Top = .Top
                    oPic.Left = .Left[/COLOR]
                    Exit For
                End If
            Next oPic
        End With
        With Range("G54")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    [COLOR=Red]oPic.Top = .Top
                    oPic.Left = .Left[/COLOR]
                    Exit For
                End If
            Next oPic
        End With
    End Sub

 

[Andrew Poulsom]

Indeed it does:

Private Sub Worksheet_Calculate()
    Dim oPic As Picture
    Me.Pictures.Visible = True
    With Range("G51")
        For Each oPic In Me.Pictures
            If oPic.Name = .Text Then
                oPic.Visible = True
                oPic.Top = .Top - (oPic.Height - .Height)
                oPic.Left = .Left - (oPic.Width - .Width)
                Exit For
            End If
        Next oPic
    End With
    With Range("G54")
        For Each oPic In Me.Pictures
            If oPic.Name = .Text Then
                oPic.Visible = True
                oPic.Top = .Top - (oPic.Height - .Height)
                oPic.Left = .Left - (oPic.Width - .Width)
                Exit For
            End If
        Next oPic
    End With
End Sub

 

[CraigFord]

Indeed it does:

Private Sub Worksheet_Calculate()
    Dim oPic As Picture
    Me.Pictures.Visible = True
    With Range("G51")
        For Each oPic In Me.Pictures
            If oPic.Name = .Text Then
                oPic.Visible = True
                oPic.Top = .Top - (oPic.Height - .Height)
                oPic.Left = .Left - (oPic.Width - .Width)
                Exit For
            End If
        Next oPic
    End With
    With Range("G54")
        For Each oPic In Me.Pictures
            If oPic.Name = .Text Then
                oPic.Visible = True
                oPic.Top = .Top - (oPic.Height - .Height)
                oPic.Left = .Left - (oPic.Width - .Width)
                Exit For
            End If
        Next oPic
    End With
End Sub

do i need to replace

oPic.Top = .Top
with
oPic.Height = .Height

 

[Andrew Poulsom]

No, use the code I posted.