Display Picture in frame on userform follwing hyperlink?

[Simon Lloyd]

Hi all, i have a userform which changes the contents of many textboxes dependant on combobox1, on the userform i have added a picture frame and would like the picture here to get updated when combobox1 is changed the value i am looking to use to change the picture is a cell that contains a hyperlink to the relevant picture, this is what i have tried..and variations of it!

Image1 = Application.FollowHyperlink(Sheets("Employee Details").Range(StS).Offset(0, 9))

Sts is an address returned using the find method and the offset above contains the hyperlink, Image1 is the name of the frame on the userform, all this resides in the Combobox1_Change.

Any ideas?

Regards,
Simon
P.s the picture would need to be resized to fit the frame as they are in different resolutions

[Simon Lloyd]

I can follow the hyperlink from the userform like this:

ActiveWorkbook.FollowHyperlink Address:=Sheets("Employee Details").Range(StS).Offset(0, 9).Hyperlinks(1).Address

but i still can't get it to appear in Image1 frame on the userform!

Regards,
Simon

[Bob Phillips]

Try this

Private Sub Image1_Click()
Sheets("Employee Details").Range(StS).Offset(0, 9).Hyperlinks(1).Follow
End Sub

[Simon Lloyd]

Good afternoon Bob, i had tried this previously and on click the pic is displayed in whichever viewer your pc normally displays pics in!, idealy i want the pic to be displayed within the frame, if thats not possible i will settle for a sized pic on the userform itself, if it is possible to call the desired pic to the frame how do we trigger the Click event automatically?

Regards,
Simon

[Bob Phillips]

So are you saying that the cell refers to a file on the PC, and you want to insert that into the image?

[Simon Lloyd]

This line: Sheets("Employee Details").Range(StS).Offset(0, 9) referes to a cell that contains a hyperlink to: C:\Documents and Settings\Simon\Desktop\Staff ID Photos\AE 3 NW.jpg, however as you know the actual file name could be AE n NW.jpg where n is a number, it is the image from tis address that i want to display.

Regards,
Simon

[lucas]

Hi Simon,
Here is an example of using a listbox of names to show a picture on click. Maybe you can revise it for your use....myname is a named range of names. It doesn't use your hyperlink method but.....

Private Sub listBox1_Click()
Dim EmpFound As Range
With Range("myName")
   Set EmpFound = .Find(ListBox1.Value)
      If EmpFound Is Nothing Then
         'Label1.Caption = ""
      Else
      With EmpFound
         Dim fPath As String
         fPath = ThisWorkbook.Path & "\"
         On Error Resume Next
         Image1.Picture = LoadPicture(fPath & "\" & ListBox1.Value & ".jpg")
         If Err = 0 Then Exit Sub
         Image1.Picture = LoadPicture(fPath & "nopic.gif")
      End With
   End If
End With
End Sub

[Simon Lloyd]

if it makes life easier the address is always the same and the ID number can be picked up from TextBox1 as this will change on combobox1_change to display AE n NW

[Bob Phillips]

Me.Image1.Picture = LoadPicture(ActiveWorkbook.Worksheets("Employee Details").Range(StS).Offset(0, 9).Hyperlinks(1).Address)
Me.Repaint

[Simon Lloyd]

thanks Lucas that kinda helped when i used this line

Image1.Picture = LoadPicture("C:\Documents and Settings\Simon\Desktop\new pics\Staff ID Photos" & "\" & TextBox1.Value & ".jpg")

but the pc showed the pic in its normal viewer after closing then it displayed in the userform.

Bob, right on the button!....indebted as always!

Kind regards,
Simon

[lucas]

No problem Simon.....not quite awake and just threw that in as an alternative...