当使用公式生成超链接时,打开Excel超链接的VBA不起作用 [英] VBA to open Excel hyperlink does not work when hyperlink generated with a formula
问题描述
似乎有一个使用公式生成的Excel超链接的错误。我使用Excel 2010.我有一个包含网址的单元格的电子表格,我的目标是做以下两件事:
- 转这些细胞变成超链接。
- 创建一个键盘快捷方式来打开这些超级链接,所以我不必使用鼠标。
要做#1,最初我刚刚使用函数 = HYPERLINK()
。所以,我的网址是列 A
,我使用这个公式在列 B
中进行超链接。 >
要做#2,我创建了以下宏,它应该用键盘快捷键打开超链接 Ctrl + H :
Sub Open_Hyperlink()
pre>
'
'Open_Hyperlink宏
'
'键盘快捷方式:Ctrl + h
'
Selection.Hyperlinks(1).Follow NewWindow:= False,AddHistory:= True
End Sub
问题是,这个宏似乎只适用于没有使用公式创建的超链接。例如,如果我输入一个单元格
http://www.google.com
,Excel将自动将其设为超链接,键盘快捷键工作在哪里没有公式生成的超链接。
我也注意到,当我右键单击公式生成的超链接时,下拉菜单中没有选项来打开超链接,但是还有一个选项右键单击公式不生成的超链接。
我找到了以下解决方法。而不是使用公式生成超链接,我使用了一个宏,我发现
希望对您有所帮助。祝你好运!
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:
- Turn these cells into hyperlinks.
- Create a keyboard shortcut to open these hyperlinks so I don't have to use the mouse.
To do #1, initially I just used the function
=HYPERLINK()
. So, my URLs are in columnA
, and I used this formula to make hyperlinks in columnB
.To do #2, I created the following macro which should open the hyperlink with the keyboard shortcut Ctrl+H:
Sub Open_Hyperlink() ' ' Open_Hyperlink Macro ' ' Keyboard Shortcut: Ctrl+h ' Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End Sub
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
http://www.google.com
, Excel will automatically make this a hyperlink and the keyboard shortcut macro works, where it doesn't with formula generated hyperlinks.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.
I've found the following workaround. Rather than generate hyperlinks using a formula, I used a macro which I found here.
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
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.
解决方案I'm wondering if anyone has had a similar problem, and why the formula generated hyperlinks are not working for me.
Alas, this seems to be painful truth: Excel does not add to
Hyperlinks
collection formula-generated links - below is the screen from the debugger which is pointed to=HYPERLINK("http://www.google.com/";"Google")
:I'm not sure whether this is a deliberate implementation or a bug, but yes, formula-generated links may NOT be opened using
Hyperlinks().Follow
method.However, if you're going to use keyboard shortcut for links opening, just use the following code - it will automatically convert to clickable link selected cell text and open it:
Sub Open_Hyperlink() Selection.Hyperlinks.Add Anchor:=Selection, Address:=Selection.Formula Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End Sub
Just assign any shortcut and you're ready to go) Here is the sample: https://www.dropbox.com/s/d4cie7lun22quma/FollowLinks.xlsm
Hope that's somehow helpful. Good luck!
这篇关于当使用公式生成超链接时,打开Excel超链接的VBA不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!