Create Excel Table of Contents with Shortcut Links

When you're working with multiple sheets of data in Excel, it can be easy to feel overwhelmed and lost among all the different tabs. Navigating between various sheets manually can be time-consuming and cumbersome. Fortunately, with a little VBA code, you can create a Table of Contents page with links to each of your sheets. This handy feature allows you to quickly navigate between sheets, enhancing your workflow and productivity.

Why Use a Table of Contents in Excel?

A Table of Contents is particularly useful when you have a large workbook with numerous sheets. It provides a centralized overview, enabling you to access your data more efficiently. By simply clicking on a link in your Table of Contents, you can jump to the specific sheet you need without scrolling through tabs. This feature is invaluable for reports, dashboards, or any extensive data analysis projects.

The VBA Code

To set up your Table of Contents, you can use the following VBA code. Let’s break it down step by step to understand how it works:

Sub TableofContent

    Dim i As Long

    On Error Resume Next

    Application.DisplayAlerts = False

    Worksheets("Table of Content").Delete

    Application.DisplayAlerts = True

    On Error GoTo 0

   

    ThisWorkbook.Sheets.Add before:=ThisWorkbook.Worksheets(1)

    ActiveSheet.Name = "Table of Content"

    For i = 1 To Sheets.Count

        With ActiveSheet

        .Hyperlinks.Add _

        Anchor:=ActiveSheet.Cells(i, 1), _

        Address:="", _

        SubAddress:="'" & Sheets(i).Name & "'!A1", _

        ScreenTip:=Sheets(i).Name, _

        TextToDisplay:=Sheets(i).Name

    End With

    Next i

End Sub

How the Code Works

1. Defining the Subprocedure: The code begins by naming the subprocedure TableofContent and defining a variable i to be used in the loop.

2. Deleting Previous Table of Contents: If a Table of Contents page already exists, the code deletes it to create a new one. This ensures that you have an up-to-date list of all your sheets.

3. Creating the New Table of Contents Sheet: A new sheet named "Table of Content" is added before the first worksheet in your workbook.

4. The Loop: The real magic happens in the For loop, where the code loops through each sheet in the workbook. For each sheet, a hyperlink is created in the Table of Contents. Each link directs you to cell A1 of the corresponding sheet.

Adding Navigation Back to the Table of Contents

Once you have created the Table of Contents, it’s beneficial to have a quick way to navigate back to it after clicking on any other sheet. You can achieve this by creating a simple macro as shown below:

Sub GotoMainPage()

    Sheets("Table of Content").Select

    Sheets("Table of Content").Range("A1").Select

End Sub

Assigning the Macro to a Shape

After pasting the GotoMainPage code into a module, you can assign this macro to a shape of your choice in Excel. This allows you to quickly return to your main Table of Contents page with just a click, improving your navigation experience.

Important Note on Saving

Please keep in mind that once you have applied these macros, Excel will prompt you to save your file as an "XLSM" (macro-enabled) file. This is necessary to preserve the functionality of the macros. Don’t worry; there will be no changes to your existing data; it will simply be saved as a macro-enabled version.

Conclusion

Creating a Table of Contents with shortcut links in Excel can significantly improve your efficiency when dealing with large datasets across multiple sheets. This VBA solution provides a user-friendly way to navigate your workbook, making your data management process smoother and more organized.

If you liked this project, please don’t forget to share it with your colleagues and leave a comment below! Your feedback is invaluable, and we appreciate hearing from you!

Share:

Read More

Paste URLs as Images in Excel with VBA

 In this blog, we will create an Excel VBA code that will turn any image URL into actual images in Excel. 

Here is the full code : We will break down the code to undestand how it works.

Sub URLPictureInsert

    Dim rng As Range

    Dim cell As Range

    Dim Filename As String

    Dim theShape As Shape

    Dim xRg As Range

    Dim xCol As Long, k As Long

    On Error Resume Next

    Application.ScreenUpdating = False

   

    Set rng = Application.InputBox("Select the cells with hyperlinks.", , , , , , , 8)

   k = Application.InputBox("What should be the column difference between links and

Result column? Example: Links in Column C (3.column), result column E (5.column) then

you should type 2 here. ", , , , , , , 1)

    For Each cell In rng

            Filename = cell

                If InStr(UCase(Filename), "JPG") > 0 Or

                   InStr(UCase(Filename), "PNG") > 0 Or

                       InStr(UCase(Filename), "JPEG") > 0 Then

                        ActiveSheet.Pictures.Insert(Filename).Select

                        Set theShape = Selection.ShapeRange.item(1)

        If theShape Is Nothing Then GoTo isnill

             xCol = cell.Column + k

            Set xRg = Cells(cell.Row, xCol)

                With theShape

                    .LockAspectRatio = msoTrue

                    .Width = 200

                    .Height = 200

                    .Top = xRg.Top

                    .Left = xRg.Left

                End With

            xRg.RowHeight = 210

            xRg.ColumnWidth = 40

isnill:

            Set theShape = Nothing

           

        End If

    Next

    Application.ScreenUpdating = True

    MsgBox ("Images pasted successfully.")

End Sub

   First of all, we named the macro as URLPictureInsert  and defined the necessary variables, using Dim keyword. 

   Then we created a range variable called rng that gets the value from the selection of the user. So if user selects the cells with URLs in it, we will be able to set our range variable with that selection.

   Then we created a variable called k to let user decide which cell are we going to paste the images. 

   After that, we have created a For loop that checks whether the URLs contains JPEG,JPG or PNG in the string.

   If nothing found, the loop will not paste anything for that URL. However, if the condition above is met, then we are going to paste that image to the user specified column and the active row.

   We can set some of the features of the image, like width, height, AspectRatio etc. depending on the needs. We can also set the column and row width and height of the cells that contains images. 

   After going through each URL from the user selection, loop will be finished with a success message. 

Note: This code will work on URLs with JPEG, JPG,PNG  URLs. However, you can add other conditions to the if the statement

   Now you can use this code snippet on any of your files. Just copy this code and paste it on the file you want.

   Also please keep in mind that, once you have applied these macros, Excel will ask you to save your file as "XLSM" ( macro enabled). There will be no change in your file and data in it. It will be just macro enabled version of your file.

If you liked this project, please don't forget to share and leave a comment below.

 

Share:

Read More