hyperlink value vs. displayed text

[Newblet]

I am trying to create an asset tracking sheet that includes hyperlinks to photos of the assets being tracked. The user stores a .jpg of the asset in one specific filefolder before getting into the tracking sheet. I have a userform with a button that takes the user to the filefolder where all of our photos are stored. They then pick the appropriate photo. Doing so returns the whole UNC as a text string back to a textbox on the form. When another button is clicked, the text in the textbox, including the whole UNC for the file location, is forwarded to a spreadsheet as a hyperlink. The spreadsheet displays the whole UNC. I'd like to be able to have the hyperlink work but only display "x.jpg" instead of the whole UNC.

What do I need to add or change to do that?

So far, the button code is:

Private Sub cmdHyper_Click()
ChDrive "C"
ChDir "C:\Documents and Settings\me\Desktop\Safety\Asset_Photos"
Dim jpgName$, arr
jpgName$ = Application.GetOpenFilename(FileFilter:="All Files,*.jpg", Title:="Look for .jpg file")
arr = Split(jpgName$, "\")
Me.txtHyper.Value = jpgName$
End Sub

The code that populates the spreadsheet with the hyperlink is:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("AssetByDist")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1).Formula = "=Hyperlink(""" & Me.txtHyper.Value & """)"
Me.txtHyper.Value = ""
Me.txtHyper.SetFocus
End Sub

 

[DonkeyOte]

If you were to move your variable declarations to the head of your module you could make use of both:

Dim jpgName$, arr

Private Sub cmdHyper_Click()
    ChDrive "C"
    ChDir "C:\Documents and Settings\me\Desktop\Safety\Asset_Photos"
    jpgName$ = Application.GetOpenFilename(FileFilter:="All Files,*.jpg", Title:="Look for .jpg file")
    arr = Split(jpgName$, "\")
    Me.txtHyper.Value = jpgName$
End Sub

Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("AssetByDist")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ws.Cells(iRow, 1).Formula = "=Hyperlink(""" & jpgName$ & """,""" & arr(UBound(arr)) & """)"
    Me.txtHyper.Value = ""
    Me.txtHyper.SetFocus
    Set ws = Nothing
End Sub

In terms of Variable scope see: http://support.microsoft.com/kb/141693