使用 Excel VBA 删除超链接时如何保留单元格格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10586341/
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
How do you preserve cell formatting when removing a hyperlink with Excel VBA?
提问by Jon Crowell
Removing a hyperlink from a cell also removes formatting.
从单元格中删除超链接也会删除格式。
Selection.Hyperlinks.Delete
Is there a way to preserve the formatting, or do I need to reapply it after removing the hyperlink?
有没有办法保留格式，或者我需要在删除超链接后重新应用它？
采纳答案by Symen
I found the solution was to call
我发现解决方案是打电话
Range.ClearHyperlinks
instead of
代替
Range.Hyperlinks.Delete
the former clears the hyperlinks and leaves the formatting intact. Reference here: https://msdn.microsoft.com/en-us/library/office/ff194741.aspx
前者清除超链接并保持格式不变。参考这里：https: //msdn.microsoft.com/en-us/library/office/ff194741.aspx
回答by Chris Sels
The above solutions give problems if you work with merged cells. This works arround that problem
如果您使用合并的单元格，上述解决方案会出现问题。这适用于该问题
data = Selection.value
Selection.Value = "" 'this removes the hyperlink
Selection.Font.Underline = xlUnderlineStyleNone 'remove the hyperlink underlining
With ActiveCell.Font 'replace hyperlink blue with black
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Selection.Value = data
回答by Ben Strombeck
I know that this is a little strange as well... not sure if this will be acceptable for you or not. Try this:
我知道这也有点奇怪……不确定这对你来说是否可以接受。尝试这个：
Selection.Hyperlinks(1).Address = ""
This pretty much acts like there is no hyperlink. The only difference is that your cursor changes to a hand instead of the default plus sign, but you can still click it like any other cells.
这几乎就像没有超链接一样。唯一的区别是您的光标变为手形而不是默认的加号，但您仍然可以像单击任何其他单元格一样单击它。
回答by Vashkar
ActiveCell.Style = "Normal"
With other words: You reapply the style that should be there. Whereas "Normal" can be changed in the name of either cellstyle present in Excell. If you want your own style to be applied with this way, add it to the cellstyles list.
换句话说：您重新应用应该存在的样式。而“正常”可以在 Excell 中存在的任一单元格样式的名称中更改。如果您希望以这种方式应用您自己的样式，请将其添加到 cellstyles 列表中。
If you want to go a step further. You can take the cellstyle already there, catch it in a string and reapply.
如果你想更进一步。您可以使用已经存在的 cellstyle，将其夹在字符串中并重新应用。
Dim sStyleName as String
sStyleName = ActiveCell.Style 'Capture the current cellstyle
ActiveCell.Hyperlinks.Delete 'Remove the hyperlink
ActiveCell.Style = sStylename 'Reapply the cellstyle used before
回答by Jon Crowell
I couldn't figure out how to prevent the formatting from getting zapped. The workaround I came up with was to copy the cell before removing the hyperlink and then pasting the format back to the cell after I deleted the hyperlink:
我不知道如何防止格式被破坏。我想出的解决方法是在删除超链接之前复制单元格，然后在删除超链接后将格式粘贴回单元格：
' copy cell to remote location to "remember" formatting
ActiveCell.Copy Sheets("Templates").Range("a1")
' remove hyperlink
ActiveCell.Hyperlinks.Delete
' reapply formatting
Sheets("Templates").Range("A1").Copy
ActiveCell.PasteSpecial Paste:=xlPasteFormats
Show me a better way of doing this and I'll accept your response as the answer.
告诉我一个更好的方法，我会接受你的回答作为答案。
回答by Arshjot Singh Gill
For Each cll In Selection
cll_val = cll.Value
cll.ClearContents
cll.Value = cll_val
With cll.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.Underline = xlUnderlineStyleNone
End With
Next
This also helps when you don't want to copy paste information to/from another location. Have't tried this on merged cells, but I guess this should work.
当您不想将粘贴信息复制到/从另一个位置复制时，这也有帮助。还没有在合并的单元格上尝试过这个，但我想这应该可行。
回答by bs0d
You can also apply the hyperlink to a shape that is transparent and overlays the cell in which you desire a hyperlink.
您还可以将超链接应用到透明的形状并覆盖您想要超链接的单元格。
The drawback here is that the cursor changes with a mouseover when not linked. And the shape can be moved by the end user and deleted as well.
这里的缺点是光标在未链接时随着鼠标悬停而改变。最终用户也可以移动和删除形状。
回答by Guest1
Dim temp As Variant
temp = (RangeObject).Interior.Color
(RangeObject).Hyperlinks.Delete
(RangeObject).Interior.Color = temp
