vba 在 Excel 单元格中创建超链接?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/10181995/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a hyperlink within an Excel cell?
提问by llawliet
Is it possible to create a hyperlink within an Excel cell which only uses a section of the cell text for the clickable link? I.E. would the below table mockup represent something that can be easily built in Excel 2010?
是否可以在 Excel 单元格中创建一个超链接,它只使用单元格文本的一部分作为可点击链接?IE 下表模型是否代表可以在 Excel 2010 中轻松构建的内容?
一个模型 http://dl.dropbox.com/u/14119404/misc/Microsoft%20Excel%20-%20Book1_2012-04-16_14-24-47.jpg
I know that an entire cell can be made into a hyperlink easily, but not a specific part of the cell as far as I know.
我知道可以轻松地将整个单元格制作成超链接,但据我所知,不是单元格的特定部分。
By hyperlink I also refer to either
通过超链接,我也指的是
- (a)another cell or,
- (b)a web URL.
- (a) 另一个单元格或,
- (b) 一个网址。
Thanks
谢谢
采纳答案by Tmdean
This isn't possible in Excel. Hyperlinks are associated with entire cells.
这在 Excel 中是不可能的。超链接与整个单元格相关联。
If you look at the documentation for the Excel hyperlink object, you can see that it's associated with a Range. If it were possible to associate hyperlinks with a span within the cell, the Hyperlink object would need to have an associated Range and Charactersobject.
如果您查看Excel 超链接对象的文档,您会发现它与一个范围相关联。如果可以将超链接与单元格内的跨度相关联,则超链接对象将需要具有关联的范围和字符对象。
回答by Tim Williams
After creating the hyperlink you could format the text in the cell so that only the words of interest are underlined/blue. The hyperlink will still work, but obviously you can still have only one link per cell, and clicking anywhere in the text will trigger the hyperlink.
创建超链接后,您可以格式化单元格中的文本,以便只有感兴趣的单词带有下划线/蓝色。超链接仍然有效,但显然每个单元格仍然只能有一个链接,单击文本中的任意位置将触发超链接。
For example:
例如:
Sub Tester()
Dim rng As Range
Set rng = ActiveSheet.Range("A1")
rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="", SubAddress:= _
"Sheet1!A10", TextToDisplay:="this is long text"
With rng.Font
.ColorIndex = xlAutomatic
.Underline = xlUnderlineStyleNone
End With
With rng.Characters(Start:=9, Length:=4).Font
.Underline = xlUnderlineStyleSingle
.Color = -4165632
End With
End Sub
回答by Dino
I needed to link to a filename displayed in a cell, so here is what worked for me:
我需要链接到单元格中显示的文件名,所以这对我有用:
ActiveSheet.Hyperlinks.Add Anchor:=Cells(row, column), Address:=file.Path, TextToDisplay:=file.Path
回答by HeavyDNickell
The above one liner was very helpful... since I'm new, I couldn't comment. So here is my variation of the above that takes each row on a worksheet and builds a URL from a value on the row.
上面的一个衬垫非常有帮助......因为我是新来的,我无法发表评论。所以这是我上面的变体,它获取工作表上的每一行并从该行上的值构建一个 URL。
CHGRow = 3
Worksheets("Page 1").Select
Cells(CHGRow, 1).Select
Do Until Application.CountA(ActiveCell.EntireRow) = 0
URLVal = "https://our_url_here?some_parameter=" & Cells(CHGRow, cNumber)
URLText = Cells(CHGRow, cNumber)
ActiveSheet.Hyperlinks.Add Anchor:=Cells(CHGRow, cURL), Address:=URLVal, TextToDisplay:=URLText
CHGRow = CHGRow + 1
Cells(CHGRow, 1).Select
Loop
回答by cardscrazyinwil
I'd just make your one row into two rows, merge the cells in the columns you need to have it appear to be a single row and when you get to the cell that needs the hyperlink then you put the words on the top cell and the link in the cell below. It will look fine as a non-techy workaround.
我只是将你的一行变成两行,合并你需要的列中的单元格,让它看起来是一行,当你到达需要超链接的单元格时,你把单词放在顶部的单元格上,然后下面单元格中的链接。作为一种非技术性的解决方法,它看起来不错。