title: VBA to open Excel hyperlink does not work when hyperlink generated with a formula
body: <p>There seems to be a bug with Excel hyperlinks which are generated with a formula.  I'm using Excel 2010.  I have a spreadsheet with cells containing URLs, and my goal is to do the following two things: </p>
  
  <ol>
  <li>Turn these cells into hyperlinks. </li>
  <li>Create a keyboard shortcut to open these hyperlinks so I don't have to use the mouse.</li>
  </ol>
  
  <p>To do #1, initially I just used the function <code>=HYPERLINK()</code>.  So, my URLs are in column <code>A</code>, and I used this formula to make hyperlinks in column <code>B</code>.</p>
  
  <p>To do #2, I created the following macro which should open the hyperlink with the keyboard shortcut <kbd>Ctrl</kbd>+<kbd>H</kbd>:</p>
  
  <pre><code>Sub Open_Hyperlink()
  '
  ' Open_Hyperlink Macro
  '
  ' Keyboard Shortcut: Ctrl+h
  '
      Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
  End Sub
  </code></pre>
  
  <p>The problem is that this macro only seems to work on hyperlinks which are not created using a formula.  For example, if I just type into a cell <code>http://www.google.com</code>, Excel will automatically make this a hyperlink and the keyboard shortcut macro works, where it doesn't with formula generated hyperlinks.  </p>
  
  <p>I've also noticed that when I right click on formula generated hyperlinks, there is no option in the drop-down menu to open the hyperlink, yet there is that option when right clicking on hyperlinks not generated by a formula.</p>
  
  <p>I've found the following workaround.  Rather than generate hyperlinks using a formula, I used a macro which I found <a href="https://stackoverflow.com/questions/2595692/how-do-i-convert-a-column-of-text-urls-into-active-hyperlinks-in-excel/2595806#2595806">here</a>.</p>
  
  <pre><code>Sub HyperAdd()
  
  'Converts each text hyperlink selected into a working hyperlink
  
  For Each xCell In Selection
      ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
  Next xCell
  
  End Sub
  </code></pre>
  
  <p>I'm able to use the keyboard shortcut to open the hyperlinks generated with this macro.  I'm wondering if anyone has or had a similar problem, and why the formula generated hyperlinks are not working for me.  I would prefer to use formulas to make hyperlinks in the future, since it is simpler, so if anyone knows of a way to avoid using a macro to make hyperlinks, I'd really appreciate it.</p>