Table of Contents Marco and Hyper Link Macros

This page of the website explains how to use and create a table of contents macro and how to quickly create hyperlinks to different sheets.  The VBA code that I use to create the table of contents macro is in the generic macros file.  In addition, a couple of functions that show who last saved the file and when the file was last saved are described below.  The VBA code for creating a table of contents is basic and all you really need to know is how to make a loop and use the CELLS function.

When you run the ALT, CNTL, C function from the generic macros, an option to make a table of contents appears.  You should first delete the exiting table of contents or sheets (you can press CNTL, A and then CNTL – ).  If you want the last saved function and the person who last saved, copy the functions below into your file.




Function File_name() As Variant
File_name = ActiveWorkbook.FullName
End Function

Function MyUDF(LastSaved1 As Boolean) As Double
 ' Good practice to call this on the first line.
 Application.Volatile (LastSaved1)
 MyUDF = Now
End Function

Function Last_save_by() As Variant
Last_save_by = ActiveWorkbook.BuiltinDocumentProperties(7)
End Function

Function LastSaved() As String
Application.Volatile (True)
LastSaved = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
Selection.NumberFormat = "dd-mmmm-yyyy hh:mm"
Selection.HorizontalAlignment = xlLeft
End Function



If you want to make your own macro, you can watch the video below.  This video demonstrates how the use a for loop along with the cells function.



Hyper Link Macros


A very helpful short-cut key that is part of excel is CNTL K.  This short-cut allows you to make a hyper link pretty quickly.  I have also created a little macro that allows you to create a hyper link.  To do this yourself, you can follow the step by step process below.  This process will allow you to create a cell link from your own cell where you link to another sheet in your workbook.

  • You should first create a formula with the range name for the cell.  Sometimes you need to put in the ” ‘ ” sequence if the sheet name contains a blank or a number etc.
  • Then you name the cell which will be used in the macro below.
  • Then you copy the macro below and adjust the range names.
  • When you adjust this macro, make sure and name the range and you can put in a button as illustrate in the screenshot below.

After the hyperlink brings you to the source page and cell, you can press the F5 key to return to the where you were.  I use this to check databases when reading in a lot of data and putting the data in different sheets. You can also assign this to a drop-down box so that the hyperlink changes whenever you make a new selection as shown in the screenshot below.  The second screenshot illustrates use of CNTL, K and the code below the screenshots can be copied into your spreadsheet so you can create the hyper link from a flexible range name.




Sub assign_links2() ' This puts a hyper link for the current company

MsgBox " Assigning Hyperlink with Range Name Assign2 " & Range("assign2")

current_calc = Application.Calculation

Application.Calculation = xlCalculationManual

range_name = Range("assign2")
range_text = Range("assign2")

' Put the range name here

Selection.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
range_name, TextToDisplay:=range_text

Application.Calculation = current_calc

End Sub



VBA Code for Creating Table of Contents

You can copy that code below to put a table of contents in your sheet.  It is long just because of formatting — the core is very simple and please do not be impressed.


For i = 1 To Sheets.Count

On Error Resume Next ' need this error check for the graph sheets


' Gets the name and puts in array sht_name

sht_name(i) = ActiveSheet.Name ' save the sheet name in an array for display

If (i < num) Then ActiveSheet.Next.Select
Next i

' Go back to contents sheet