Databases
StackOverflow2013
Postsdbo
POVBA to open Excel hyperlink does not work when hyperlink generated with a formula
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA to open Excel hyperlink does not work when hyperlink generated with a formula
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USSteve
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVBA to open Excel hyperlink does not work when hyperlink generated with a formula
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POVBA to open Excel hyperlink does not work when hyperlink generated with a formula
      1. USchuff
      1. VTFavorite
    3. VO
      singulars
      1. POVBA to open Excel hyperlink does not work when hyperlink generated with a formula
      1. This table or related slice is empty.
      1. VTUpMod
    1. COLooks like I'm having the same problem: http://stackoverflow.com/q/14574281/1286571
      singulars
      1. POVBA to open Excel hyperlink does not work when hyperlink generated with a formula
      1. USForeverWintr
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload