Geek asks and answers

How to save cell formatting when deleting a hyperlink using Excel VBA?

Removing the hyperlink from the cell also removes the formatting.

Selection.Hyperlinks.Delete

Is there a way to keep the formatting, or do I need to reapply it after deleting the hyperlink?

+3
source share
8 answers

I found a solution to call

Range.ClearHyperlinks

instead

Range.Hyperlinks.Delete

the first clears the hyperlinks and leaves the formatting intact. Link here: https://msdn.microsoft.com/en-us/library/office/ff194741.aspx

+3
source

I know this is a little strange ... not sure if this will be acceptable to you or not. Try the following:

Selection.Hyperlinks(1).Address = ""

. , , , .

+2

, .

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
+2

, zapped. , , , , :

' 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

, .

+1
ActiveCell.Style = "Normal"

: , . , "" , Excell. , , cellstyles.

. , .

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
+1
Dim temp As Variant
temp = (RangeObject).Interior.Color
(RangeObject).Hyperlinks.Delete
(RangeObject).Interior.Color = temp
0

, .

, , . .

0
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

, / . , , .

0

More articles:


All Articles