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, Jul 15 '15 at 12:41
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, Jul 15 '15 at 12:43
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, Jul 15 '15 at 12:46
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, Jul 15 '15 at 12:49
So basically everything is working and I currently want to add in a macro to automatically click the sharepoint link after the barcode is scanned so that no actions or anything are necessary. — Namorg, Jul 15 '15 at 12:50
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, Jul 15 '15 at 12:57
Thanks for the advice. I hope my post is a little clearer now. — Namorg, Jul 15 '15 at 13:02

R3uK · Accepted Answer · 2015-07-15T13:30:28.217

1

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!

edited Jul 15 '15 at 13:30

answered Jul 15 '15 at 13:16

R3uK

14,417
7
43
77

When I tried to implement that change into my code I receive a "Compile error: Type mismatch" with the debug screen pointing to : http://imgur.com/5I2KJZN – Namorg Jul 15 '15 at 13:22
1

My bad... there is actually no reason to keep it as a function, convert it to a regular sub like in the edit! ;) – R3uK Jul 15 '15 at 13:30
1

Wow that worked absolutely beautifully. Your a saint, thank you so much! – Namorg Jul 15 '15 at 13:32
1

Ahah! A saint, maybe not, but I do love to see that I helped! ;) Enjoy and see you someday on the site! – R3uK Jul 15 '15 at 13:42
One more thing if you could help. That worked perfectly, however now if I save the document with a couple barcodes scanned in, close it, and re-open it, it triggers the macros and opens several on my browser. Any way of tweaking this code so that it does not trigger on opening of the file? – Namorg Jul 15 '15 at 13:44
1

Easiest way I can think of would be to declare a `Public Boolean` and set it to **False** in `Workbook_Open` and test it into `Worksheet_Change` to `Exit Sub` if it is set to False, and also reset it to **True** in `Workbook_Activate`. That might be enough. And btw, as your are working with **Objects variables, don't forget to free them** once they are used : `Set KeyCells = Nothing` – R3uK Jul 15 '15 at 13:52
Im no quite sure what you mean by delcaring a Public Boolean or really the rest of your comment for that matter. Im still pretty new to VBA and especially excel VBA. – Namorg Jul 15 '15 at 14:23
1

http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba and http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=109:excel-vba-events-event-procedures-handlers-triggering-a-vba-macro&catid=79&Itemid=475 should help you to through it! ;) – R3uK Jul 15 '15 at 14:31

Excel hyperlink follow macro after worksheet_change event

1 Answers1