title: Excel hyperlink follow macro after worksheet_change event
body: <p>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 &amp; 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?</p>
  
  <p>a screen shot of said spreadsheet</p>
  
  <p><a href="https://i.stack.imgur.com/VB4cW.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VB4cW.jpg" alt="screenshot"></a></p>
  
  <p>Worksheet change code:</p>
  
  <pre><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
  </code></pre>
  
  <p>parse code:</p>
  
  <pre><code>Sub textsplit(rng As Range)
  Dim c As Range, arr
  
  For Each c In rng.Cells
      If Len(c.Value) &gt; 0 Then
          arr = Split(c.Value, " ")
          c.Offset(0, 1).Resize(1, UBound(arr) + 1).Value = arr
      End If
  Next c
  
  End Sub
  </code></pre>
  
  <p>Hlink code: </p>
  
  <pre><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
  </code></pre>
  
  <p>used in the sharepoint link cell with this formaula which find and extract the hyperlink of a cell on a different page.       <code>=HYPERLINK(HLink(INDEX(Table_owssvr[Name],MATCH(Parse!C3&amp;"_"&amp;Parse!D3,Table_owss vr[Name],0))),INDEX(Table_owssvr[Name],MATCH(Parse!C3&amp;"_"&amp;Parse!D3,Table_owssvr[Name],0)))</code></p>
  
  <p>EDIT (for clarity): All of this code that I posted is currently working, <strong>the problem I am having is automatically</strong> (with the only user input being to scan said barcode into excel) <strong>following the hyperlink generated in column H</strong>.</p>
  
  <p>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."</p>
  
  <p>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. </p>