How to send a hyperlink for a user selected photo

[Newblet]

I have a command button (cmdHyper) that takes the user to a specific file folder and asks them to select a photo of their asset. It then takes the name of the photo and sends it to the spreadsheet. I would like to be able to have it return a hyperlink instead of just the name of the file, but can't figure it out.

Here's what I have so far...

Private Sub cmdHyper_Click()
ChDrive "C"
ChDir "C:\Documents and Settings\m2odlsap\Desktop\Dev. Proj\Water Safety\WS_Asset_Photos"
Dim jpgName$, arr
jpgName = Application.GetOpenFilename(FileFilter:="All Files,*.jpg", Title:="Look for .jpg file")
arr = Split(jpgName, "\")
With Me.txtHyper
If arr(UBound(arr)) = "False" Then
.Text = ""
Else
.Text = "=HYPERLINK(Left(arr(UBound(arr)), Application.Find("", arr(UBound(arr))) - 1)"
End If
End With
End Sub


I'll admit to having found the basework for the code on the net and modifying it to fit my filenames. I keep getting an OBJECT error with the second .Text line. Any help would be appreciated greatly.

 

[Derek Brown]

Hello and welcome to The Board.
It is not clear whether you want the hyperlink in the worksheet or the form but, in case it helps, here is how I would take the filename, with the path in full, in a cell and convert it to a hyperlink:

' ws has already been set to the name of the worksheet
' rng and rng2 have been declared as ranges
Set rng = ws.Range("A2:A50")
For Each rng2 in rng
      rng2.Hyperlinks.Add rng2, rng.Value,,,"Filename"
Next rng2
' note that "Filename" would be replaced by the name of the String
' containing the name of the file in the format that you want it displayed.

I have not tested that code extract as I have just adapted it slightly from that used in one of my workbooks.