Increase font size of links

I would like to increase the font size of the links that populate my index sheet in excel but don't know what the code is or where to put the code. I have tried a few things but none work. Here is my code:

Option Explicit

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 1

Me.Columns(1).ClearContents

With Me.Cells(1, 1)
.Value = "INDEX"
.Name = "Index"
.Font.Bold = True
.Font.Name = "Arial Black"
.Font.Size = 18
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Hyperlinks.Add Anchor:=.Range("B1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With

With wSheet.Range("B1")
.Name = "Start_" & wSheet.Index
.Font.Bold = True
.Font.Name = "Arial Black"
.Font.Size = 18
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet

End Sub

With this code, the only font that increases is the word 'index' on my index sheet. Please help!

RE: Increase font size of links

Hi,

I hope you do not mind, but I rewrote your subroutine in my way. I also hope, that I understood your requirements correctly.

To use the subroutine:

 1) insert standard code module into your project and paste the subroutine there;

 2) call it from desired Worksheet_Activate event (or from somewhere else), for example:

' ************************* ' ************************* '
Private Sub Worksheet_Activate()

    Call CreateIndexPage(IndexPage:=Me)

End Sub
' ************************* ' ************************* '
 

And here's the subroutine:

' ************************* ' ************************* '
Sub CreateIndexPage(IndexPage As Worksheet)

    Dim intIndex As Integer
    Dim oWsh As Worksheet
    Dim lngRowN As Long
    Dim intTemp As Integer

   
    Application.ScreenUpdating = False
   
    With IndexPage
        .Cells.Clear

       
        With .Range("A1")
            .Value = "INDEX"
            .Name = "Index"
            .Font.Bold = True
        End With

       
        intIndex = .Index
        lngRowN = 2

       
        For Each oWsh In .Parent.Worksheets
            intTemp = oWsh.Index

           
            If intTemp <> intIndex Then
                With oWsh
                    .Hyperlinks.Add Anchor:=.Range("B1"), Address:="", _
                        SubAddress:="Index", TextToDisplay:="Back to Index"

                       
                    With .Range("B1")
                        .Name = "Start_" & intTemp
                        .Font.Bold = True
                        .Font.Name = "Arial Black"
                        .Font.Size = 18
                    End With
                End With

               
                .Hyperlinks.Add Anchor:=.Cells(lngRowN, 1), Address:="", _
                    SubAddress:="Start_" & intTemp, TextToDisplay:=oWsh.Name

               
                lngRowN = lngRowN + 1
            End If
        Next oWsh

       
        With .UsedRange
            .Font.Name = "Arial Black"
            .Font.Size = 18
        End With
    End With

   
    Application.ScreenUpdating = True

End Sub
' ************************* ' ************************* '

 

Best regards.

 

P.S. Some advice. Try to avoid using of "l" (lower case of L) like variable, because in some text editors is very similar to "1" and code line like Cells(l, 1) can be a bit confusing. Unfortunately, the current editor cannot be example of what i mean :) .

Increase Font size

OMG!! I have been trying to figure this out for days! Thank you so much!!! You have MADE MY DAY!!!!

Vishesh's picture

Hyperlink style

If you are using Excel 2007 or above then
1. Select any of the hyperlink cell in the sheet
2. In the menu goto Home->Cell Styles and Right click the 'Hyperlink' style
3. Select Font option and click Format
4. Click on the Font tab and choose the font size.

Increase font size of links

Thank you for your suggestion! The only thing is that once a sheet is inserted into my workbook, the index updates and the formatting goes back to whatever it was set as in my code. I think I need to code the links to show as font arial black, size 18 rather than manually formatting it every time..