CODEWITHSUNDEEP
vbaexcelhyperlink
Namorg
Namorg

Reputation: 37

Excel hyperlink follow macro after worksheet_change event

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

screenshot

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.

Upvotes: 2

Views: 1671

Answers (1)

R3uK
R3uK

Reputation: 14537

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!

Upvotes: 1

Related Questions