Unable to change link font (keeps changing back to default)

[Ironman]

Hi

Cell B1 contains a link with the word ACTIVITY. I change the font to Segoe UI size 9 and it works fine until I next open the workbook, when it reverts to the default Comic Sans MS and it's driving me nuts.

I recently changed the default hyperlink font to Comic Sans MS in the Excel Styles toolbar, which I guess is what's causing the problem but I still want all other current and future links to be in Comic Sans MS font.

Help appreciated!

Many thanks

 

[Michael M]

Is that the proper name of the Style ??
It look more like a font name ?
If you created a new style using an existing font name I think it would fail.

 

[Ironman]

Ahh, I didn't know about that, thanks Michael, that's something else I've learnt from you.

I named and applied the new style IronMan to that cell, which worked fine...until I reopened the workbook, when I saw it change back to Comic Sans MS right in front of my eyes during the open event. I just can't see what's happening in the open event that could be causing that to happen.

 

[Michael M]

In that case I'm guessing the Hyperlink style will take precendence over any other , if the cell is actually a hyperlink cell
AND
The cells style doesn't seem that much different ??

 

[Ironman]

This is what's happening with that cell and thanks to your train of thought, I've just located the error!

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
 ' The following creates a link in Training Log B1 back to the last amended cell in the workbook

    Dim ws As Worksheet
    Set ws = Worksheets("Training Log")
   
    ws.Hyperlinks.Add Anchor:=ws.Range("B1"), Address:="", SubAddress:= _
          "'" & sh.Name & "'" & "!" & target.Address(0, 0), ScreenTip:="Go to last active cell", TextToDisplay:="ACTIVITY"
    With ws.Range("B1")
.Font.FontStyle = "Segoe UI"
.Font.Size = 9
.Font.Bold = True
End With
End Sub

I changed .Font.FontStyle = "Segoe UI" to .Font.Name = "Segoe UI" and it works fine now.

Thanks a lot Michael!

 

[Michael M]

Yeah, kinda thought that would be the issue...glad you got it sorted...