0

I have written a macro that performs a VLookup but I need it to return a hyperlink and not just a text string. There is a hyperlink in the range so all I need it to do is copy it into my result.

How can I change this VBA?

Sub check()
    On Error GoTo MyerrorHandler:
    Dim claim_number As String
    Dim here As String
    claim_number = InputBox("please enter claim number")
    If Len(claim_number) > 0 Then
    Set myrange = Range("claims")
    here = Application.WorksheetFunction.VLookup(claim_number, myrange, 3, False)
    MsgBox "Claim has been investigted, info is here " & here

    Else
    MsgBox "You didn't enter a claim number"
    End If
    Exit Sub

    MyerrorHandler:
    If Err.Number = 1004 Then
    MsgBox "Claim has not been invsetigated"
    End If

End Sub

The VLookup works fine but instead of copying the hyperlink from my range it just returns the text. I have tried everything but cannot get it to work.

Community
  • 1
  • 1
Stoke_man
  • 11
  • 4
  • You cannot have hyperlink on msgbox - https://stackoverflow.com/questions/28705916/putting-a-hyperlink-in-a-messagebox – Alex Oct 04 '17 at 21:23
  • Hyperlink to where? why do you need a hyperlink? –  Oct 04 '17 at 22:20
  • You can't have a hyperlink on a `MsgBox`, but you could put up a `vbYesNo` box and ask the user if they want to follow the hyperlink, then programatically send them to it. – dwirony Oct 05 '17 at 03:52
  • Thanks for your replies. How do i put a vbYesNo box on to the end of my macro with the option to programatically send them the relevant hyperlink? – Stoke_man Oct 05 '17 at 06:47
  • I wanted some way of giving the option to view or be redirected the relevent file that was returned by the vlookup. Is that possible? – Stoke_man Oct 05 '17 at 07:36

0 Answers0