PDA

View Full Version : [SOLVED] Display Picture in frame on userform follwing hyperlink?



Simon Lloyd
05-06-2007, 03:14 AM
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
05-06-2007, 05:19 AM
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
05-06-2007, 07:54 AM
Try this


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

Simon Lloyd
05-06-2007, 08:13 AM
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
05-06-2007, 08:15 AM
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
05-06-2007, 08:26 AM
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
05-06-2007, 08:27 AM
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
05-06-2007, 08:29 AM
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
05-06-2007, 08:30 AM
Me.Image1.Picture = LoadPicture(ActiveWorkbook.Worksheets("Employee Details").Range(StS).Offset(0, 9).Hyperlinks(1).Address)
Me.Repaint

Simon Lloyd
05-06-2007, 08:36 AM
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
05-06-2007, 08:50 AM
No problem Simon.....not quite awake and just threw that in as an alternative...