Create Excel Table of Contents with Shortcut Links

   When you're working with sheets of data on Excel, it is easy to get lost among the sheets. However, with this little VBA code, you can create a Table of Contents Page with link to each of the sheets. Then you can easily navigate between sheets back and forth. 

Here is the code below. We are going to break down 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

  First, we named the subprocedure as TableofContent  and defined a variable called i to use it in the for loop.

  Then if exists, we are deleting the previous Table Of Content page to create a new one.

   Next, we are stating that the new Table of Content sheet, will be placed before the first sheet.

   Then the real For loop magic happens. We are looping through each sheet index and create a hyperlink for them in the Table of Content sheet. 

   Now with this code, you can easily create a Table of Content and navigate through sheets.

 However, after you click and activate any sheet, you should be able to navigate back to the Table of Content sheet to easily go back and forth. To do that, you can create a macro with just two lines below and assign it to a shape.

Sub GotoMainPage()

    Sheets("Table of Content").Select

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

End Sub

  

 After pasting this code to a module, you can assign this macro to a shape of your choice, and your back to main page is on your service. 

   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 comment below.

 

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