Excel hyperlink follow macro after worksheet_change event

Question

I have an excel sheet that takes a barcode scan (2D/QR barcode formated like SN1234567 7654321 PA01234-5 A B C) when scanned in via a plug & play scanner and parses said barcode to individual cells to the right with the space being the delimiting character. Now after this parse I have a cell using a combination of the excel hyperlink function an Hlink macro(shown below) and a vlookup function to find a hyperlink on a different worksheet using the parsed information. Now, I'm trying to write a macro so that once this barcode is scanned and the worksheet_change even is triggered, parsed, and the hyperlink is found, the hyperlink is automatically clicked. (I'm using this system to attempt to "idiot-proof" a process in which operators fail type in numbers correctly.) anyway a screenshot of my workbook and all the code I currently am using will be below, can you please help me solve this issue?

a screen shot of said spreadsheet

Worksheet change code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range, rng As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:A10")

'Target can be a multi-cell range, so you need to account
'  for that possibility
Set rng = Application.Intersect(KeyCells, Target)

If Not rng Is Nothing Then
    'prevent re-activating this sub when splitting text...
    Application.EnableEvents = False
    textsplit Target
    Application.EnableEvents = True
End If

Exit Sub

haveError:
    Application.EnableEvents = True

End Sub

parse code:

Sub textsplit(rng As Range)
Dim c As Range, arr

For Each c In rng.Cells
    If Len(c.Value) > 0 Then
        arr = Split(c.Value, " ")
        c.Offset(0, 1).Resize(1, UBound(arr) + 1).Value = arr
    End If
Next c

End Sub

Hlink code:

Function HLink(rng As Range) As String
'extract URL from hyperlink
'posted by Rick Rothstein
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

used in the sharepoint link cell with this formaula which find and extract the hyperlink of a cell on a different page. =HYPERLINK(HLink(INDEX(Table_owssvr[Name],MATCH(Parse!C3&"_"&Parse!D3,Table_owss vr[Name],0))),INDEX(Table_owssvr[Name],MATCH(Parse!C3&"_"&Parse!D3,Table_owssvr[Name],0)))

EDIT (for clarity): All of this code that I posted is currently working, the problem I am having is automatically (with the only user input being to scan said barcode into excel) following the hyperlink generated in column H.

EDIT 2: To give a little bit more info, I just tried to add this code ActiveWorkbook.FollowHyperlink Address:=Range("H3").Address, NewWindow:=False, AddHistory:=True in the worksheet change sub after the textsplit call. This implementation yielded the error "Run-time error '-2147221014(800401ea)': Cannot open the specified file."

I am thinking that this error is due to the value of H3 being the "friendly name" portion of the hyperlink function. Meaning that when this macro tried to "click" or follow H3 it was met with the value 4512517_PA06872-1 and not the actual hyperlink needed to follow it.

What problem are you having? It's not clear from your question... — nwhaught
im trying to write a macro so that once this barcode is scanned and the worksheet_change even is triggered, parsed, and the hyperlink is found, the hyperlink is automatically clicked. But I have having trouble getting this macro to work for me. — Namorg
What behavior are you seeing? Is worksheet_change not being triggered? Can't figure out how to click the hyperlink? Getting errors? Just trying to figure out what question to answer. :-) — nwhaught
No, all the code I posted is currently working. When I scan the barcode in the scan cell it automatically parses it and the hyperlink is identified in the H column to the right of the parse. However my attempts are adding code (like this Range("H3").Hyperlinks(1).Follow (True)) to either the textsplit sub or the worksheet change event have been unsuccessful leaving me with errors like "Run -error "9" subscript out of range". Thank you so much for your time! — Namorg
Cool. It would be helpful (and attract better answers) if you would edit your question to include your exact problem (getting the error when trying to follow the hyperlink), the code that is generating the error, with the location of the error in the code identified as closely as possible. — nwhaught

R3uK R3uK · Accepted Answer · 2015-07-15T13:16:54

Ok so it is simply your HLink macro that doesn't do the proper thing, most easy way would be to directly follow the hyperlink :

Sub HLink(rng As Range)
   If rng(1).Hyperlinks.Count Then rng.Hyperlinks(1).Follow
End Sub

This will automatically open your default browser to go the URL/address of your hyperlink.

And the hard way would be to reference an IE application and open the address that you found in it and then work with it, but if it's on only to open the hyperlink, that code would be enough!

Excel hyperlink follow macro after worksheet_change event

1 Answers