List all hyperlinks in worksheet programmatically

1. List all hyperlinks in worksheet programmatically

The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" or "www". It grabs the hyperlink even if you have a hyperlink function in a cell.

Example, this sheet has two regular hyperlinks (B2:B3) and one hyperlink function (B5) and two web addresses (B7).

This is what the macro returns, the worksheet name in column A. Cell address in column B and the hyperlink in column C.

1.1. VBA Macro- list all hyperlinks in the worksheet

'Name macro
Sub ListHyperlinks()

'The SET statement allows you to save an object reference to a variable
'Save activesheet to object Asheet
Set Asheet = ActiveSheet

'Insert a new worksheet and save to object Nsheet
Set Nsheet = Sheets.Add

'Save values Worksheet, Address, and Hyperlink to cell range A1:C1
Nsheet.Range("A1:C1") = Array("Worksheet", "Address", "Hyperlink")

'Change to bold font
Nsheet.Range("A1:C1").Font.Bold = True

'Save 0 (zero) to variable i
i = 0

'Iterate through each cell in UsedRange
'UsedRange returns a Range object that represents the used range on the specified worksheet.
For Each cell In Asheet.UsedRange

'Enable error handling
On Error Resume Next

'Check if cell has a hyperlink and save the hyperlink to variable lnk
lnk = cell.Hyperlinks(1).SubAddress

'Check if no error has occurred
If Err = 0 Then

'Save worksheet name to column A based on variable i
Nsheet.Range("A2").Offset(i, 0) = Asheet.Name

'Save cell address to column B based on variable i
Nsheet.Range("B2").Offset(i, 0) = cell.Address

'Save hyperlink address to column C based on variable i
Nsheet.Range("C2").Offset(i, 0) = cell.Hyperlinks(1).Address

'Add 1 to variable i and save result to variable i
i = i + 1

'Go here if an error has occurred
Else

'Check if formula begins with "=HYPERLINK("
If Left(cell.Formula, 11) = "=HYPERLINK(" Then

'Split formula and save to variable strArray 
strArray = Split(cell.Formula, Chr(34))

'Save worksheet name to column A based on variable i
Nsheet.Range("A2").Offset(i, 0) = Asheet.Name

'Save cell address to column B based on variable i
Nsheet.Range("B2").Offset(i, 0) = cell.Address

'Save hyperlink address to column C based on variable i
Nsheet.Range("C2").Offset(i, 0) = strArray(1)

'Add 1 to variable i and save result to variable i
i = i + 1

'Go here if formulas does not begin with "=HYPERLINK("
Else

'Split cell into an array based on default delimiting value (space character) and save to array variable strArray 
strArray = Split(cell)

'Iterate through each value in array strArray
For Each vl In strArray

'Check if array value is equal to http or www
If Left(vl, 4) = "http" Or Left(vl, 3) = "www" Then

'Save worksheet name to column A based on variable i
Nsheet.Range("A2").Offset(i, 0) = Asheet.Name

'Save cell address to column B based on variable i
Nsheet.Range("B2").Offset(i, 0) = cell.Address

'Save hyperlink address to column C based on variable i
Nsheet.Range("C2").Offset(i, 0) = vl

'Add 1 to variable i and save result to variable i
i = i + 1
End If
Next vl
End If
End If

'Disable error handling
On Error GoTo 0
Next cell

'Change column widths for A:C
Nsheet.Columns("A:C").AutoFit

End Sub

Back to top

1.2. Where to put the code?

1. Press Alt +F11 to open the Visual Basic Editor (VBE).
2. Press with left mouse button on "Insert" on the top menu.
3. Press with mouse on "Module" to insert a module.
4. Paste VBA code to code window.
5. Exit VB Editor Alt + Q.

Save workbook with file extension *.xlsm (macro-enabled workbook).

Back to top

1.3. How to start macro?

1. Press Alt+F8, a dialog box appears.
2. Press with left mouse button on "ListHyperlinks" to select the macro name.
3. Press with left mouse button on "Run" button to run the selected macro.

Back to top

1.4. What happens when I run the macro?

1. A new worksheet is inserted.
2. The worksheet is populated with hyperlink data.
3. Macro ends.

Back to top

1.5. How do I find all hyperlinks in a workbook?

This macro allows you to find links in all worksheets in the active workbook.

Sub ListHyperlinksInWB()

Set Nsheet = Sheets.Add
Nsheet.Range("A1:C1") = Array("Worksheet", "Address", "Hyperlink")
Nsheet.Range("A1:C1").Font.Bold = True

i = 0
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> Nsheet.Name Then

For Each cell In sh.UsedRange
On Error Resume Next
lnk = cell.Hyperlinks(1).SubAddress
If Err = 0 Then
Nsheet.Range("A2").Offset(i, 0) = sh.Name
Nsheet.Range("B2").Offset(i, 0) = cell.Address
Nsheet.Range("C2").Offset(i, 0) = cell.Hyperlinks(1).Address
i = i + 1
Else
If Left(cell.Formula, 11) = "=HYPERLINK(" Then
strArray = Split(cell.Formula, Chr(34))
Nsheet.Range("A2").Offset(i, 0) = sh.Name
Nsheet.Range("B2").Offset(i, 0) = cell.Address
Nsheet.Range("C2").Offset(i, 0) = strArray(1)
i = i + 1
Else
strArray = Split(cell)
For Each vl In strArray
If Left(vl, 4) = "http" Or Left(vl, 3) = "www" Then
Nsheet.Range("A2").Offset(i, 0) = sh.Name
Nsheet.Range("B2").Offset(i, 0) = cell.Address
Nsheet.Range("C2").Offset(i, 0) = vl
i = i + 1
End If
Next vl
End If
End If
On Error GoTo 0
Next cell
End If
Next sh

Nsheet.Columns("A:C").AutoFit

End Sub

Back to top

1.6. How to disable automatic hyperlinking in Excel?

Tip! If you want to stop automatic hyperlinking (Excel 2010) follow these steps:

1. Press with left mouse button on "File" on the ribbon
2. Press with left mouse button on "Options"
3. Press with left mouse button on "Proofing"
4. Press with left mouse button on "AutoCorrect Options..."
5. Go to "Autoformat as you type" on the menu
6. Disable "Internet and network paths with hyperlinks"

Back to top

Back to top

2. Find cells containing formulas with literal (hardcoded) values

This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from a macro that iterates through each worksheet in a given workbook looking for hardcoded values in formulas.

A link is generated and the formula itself is displayed if a hardcoded value is found.

2.1. Find hardcoded values in formulas (Conditional Formatting)

I found this UDF in David Hager's Excel Experts E-letter (EEE) on J Walkenbach's website. You need to apply conditional formatting with a formula that uses the User Defined Function described below.

Conditional Formatting highlights cells with formulas containing hardcoded values, the image above shows which cells contain hardcoded values. They are highlighted green. For example, cell C3 contains the following formula:

=SUM(A2:A5, 5)

The second argument in the SUM function is a hardcoded value, cell C3 is highlighted.

'Name User Defined Function
Function CellUsesLiteralValue(Cell As Range) As Boolean

'Check if cell has not a formula
If Not Cell.HasFormula Then

'Save boolean value FALSE to variable CellUsesLiteralValue 
CellUsesLiteralValue = False

'Continue here if cell has a formula 
Else

'Use like operator to determine if cell formula contains hardcoded values, it returns TRUE if found.
'Characters enclosed in brackets allows you to match any single character in the string.
' The hashtag matches any single digit, the asterisk matches zero or more characters
CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()<>, ]#*"
End If
End Function

VBA: Range Formula property | Like Operator | HasFormula property | Declare Range object | If then statement

Where to put the VBA code?

Back to top

2.1.1 How can I locate cells containing formulas with literal values?

Use the UDF as your conditional formatting formula, I will explain exactly how in the next section below if you are interested. It accepts a single cell as an argument. It returns True if the cell's formula contains an operator followed by a numerical digit. In other words, it identifies cells that have a formula that contains a literal numeric value.

You can test each cell in the range, and highlight it if the function returns True. The UDF with conditional formatting highlights cells containing formulas with literals, column C. You can get an excel file at the end of this post if you are interested in this technique.

I have bolded all literal values in the formulas, column E. It seems to work with nested formulas also. Boolean values can be expressed as TRUE/FALSE but also 1/0 in Excel. Note, some formulas are built to have hard-coded arguments, like col_index_num in the VLOOKUP function.

Back to top

2.1.2 How to use the User Defined Function with Conditional Formatting

1. Go to the worksheet you want to search.
2. Press with left mouse button on the button containing a triangle to select all cells on the worksheet, see image above.
3. Go to tab "Home" on the ribbon if you are not already there.
4. Press with left mouse button on "Conditional Formatting" button, see image above. A pop-up menu appears.
5. Press with left mouse button on "New Rule...", see image above. A dialog box shows up on the screen.
   
   The image above shows the wrong cell reference C2, it should be A1.
6. Press with left mouse button on "Use a formula to determine which cells to format".
7. Type following formula: =CellUsesLiteralValue(A1)
8. Press with left mouse button on "Format..." button. Another dialog box appears.
9. Go to tab "Fill".
10. Pick a color.
11. Press with left mouse button on "OK" button.
12. Press with left mouse button on "OK" button again.

Back to top

2.2. Find hardcoded values in formulas across worksheets

I have a large workbook and I am in a hurry!

The authors want you to use it as conditional formatting formula to spot cells containing formulas with literal values, that can be tedious work with a large workbook. I would like to show you how to use the udf to build a list of all cells containing literal values, from an entire workbook.

This picture above shows you what my macro below returns, first a new sheet is inserted. It is then populated with links to all cells containing formulas with literals and their corresponding formulas.

I have reused some parts of Rick Rothstein's macro.

'Name macro
Sub FindLiteralsInWorkbook()

'Dimension variables and declare data types
Dim C As Range, A As Range, Addresses As String, i As Single
Dim cell As Range

'Add worksheet to workbook
Sheets.Add

'The SET statement allows you to save an object reference to a variable, in this case the active worksheet
Set x = ActiveSheet

'Save text "Link" to cell A1.
x.Range("A1") = "Link"

'Save text "Formula" to cell B1.
x.Range("B1") = "Formula"

'Save number 1 to variable i.
i = 1

'Iterate through worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Enable error handling
    On Error Resume Next

    'Find cells containing constants and save to object C
    Set C = sh.Cells.SpecialCells(xlConstants)

    'Check if C is empty
    If C Is Nothing Then

      'Find cells containing formulas and save to object C
      Set C = sh.Cells.SpecialCells(xlFormulas)
    
    'Continue here if C is not empty
    Else

      'Returns the union of the two cell ranges and saves to object C
      Set C = Union(C, sh.Cells.SpecialCells(xlFormulas))
    End If

    'Iterate through all the ranges in a multiple-area selection.
    For Each A In C.Areas

      'Iterate through all cells in cell range A
      For Each cell In A

         'Use User Defined Function to determine if cell contains at least one hardcoded value
         If CellUsesLiteralValue(cell) = True Then

              'Add 1 to variable i 
              i = i + 1

              'Create a hyperlink based on the cell address
              x.Hyperlinks.Add Anchor:=x.Range("A" & i), _
              Address:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, _
              SubAddress:=sh.Name & "!" & cell.Address, _
              TextToDisplay:=sh.Name & "!" & cell.Address
              x.Range("B" & i) = "'" & cell.Formula
         End If

      'Continue with next cell
      Next cell

    'Continue with next cell range
    Next A

    'Disable error handling
    On Error GoTo 0

'Continue with next worksheet
Next sh

'Resize column A:E widths 
x.Columns("A:E").AutoFit
End Sub

VBA: SpecialCells(xlConstants) | Union | Areas

Back to top

2.3. Where to put the code?

1. Copy User Defined Function CellUsesLiteralValue.
2. Go to the VB Editor (Alt + F11).
3. Go to the menu and press with left mouse button on Insert.
4. Press with left mouse button on Module.
5. Paste UDF to your workbook's code module
6. Copy macro FindLiteralsInWorkbook
7. Paste to the module. Both the UDF and macro should now be visible in the module, see image above.
8. Return to Excel.

Back to top

2.4. How to run the macro

1. Go to tab Developer on the ribbon.
2. Press with left mouse button on "Macros" button.
3. Press with left mouse button on "FindLiteralsInWorkbook" and then press with left mouse button on "Run".

Back to top

Recommended reading

Hyperlinks.Add Method (Excel)

Back to top

3. Extract cell references populated with values - VBA

This article demonstrates a macro that returns cell references for cell ranges populated with values on a worksheet.

What you will learn in this article

1. Create a macro that extracts cell references of populated cells in a worksheet.
2. Save the answer from an inputbox to a variable.
3. Loop through populated cells in a column.
4. How to use the currentregion property with an object reference.
5. Iterate through worksheet columns.
6. Append values to a variable.
7. Add values to a collection variable.
8. Insert a new worksheet and save an object reference to a variable.
9. Save the result to a given cell.

The following macro moves from column to column and checks for values. If a value is found, the current region property (Ctrl + A) is applied and the cell range address is saved. A new sheet is created and all unique distinct cell references are concatenated using a delimiting character into cell A1.

The current region is a range bounded by any combination of blank rows and blank columns. In other words, the macro creates cell references to all cell ranges populated with values.

3.1 VBA Code

'Name macro
Sub ExtractAddresses()

'Dimension variables and declare data types
Dim sht As Worksheet
Dim CurCell As Range
Dim Adr As New Collection
Dim c As Single
Dim Value As Variant
Dim result As String, delch As String

'Show inputbox and ask for a delimiting character, save to variable delch
delch = InputBox("Delimiting character:")

'The SET statement allows you to save an object reference to a variable
Set CurCell = ActiveSheet.Range("A1")

'Iterate from 1 to the number of columns in your workbook using the FOR NEXT statement
For c = 1 To Columns.Count - 1

    'The SET statement allows you to save an object reference to a variable, in this case it is next populated cell in column A. It returns the last cell if column A has no populated cells.
    Set CurCell = CurCell.End(xlDown)

    'Loop through following lines as long as cell saved to CurCell is not empty. 
    Do While CurCell.Value <> ""

        'Check if the length of the address of object CurCell with currentregion property is larger than 0 (zero)
        If Len(CurCell.CurrentRegion.Address) > 0 Then

            'Enable error handling, an error occurs if a cell reference already exists in the collection variable
            On Error Resume Next
 
            'Save address of currentregion property based on object CurCell to collection variable Adr
            Adr.Add CurCell.CurrentRegion.Address, CStr(CurCell.CurrentRegion.Address)

            'Disable error handling
            On Error GoTo 0
        End If

        'Check if the CurCell row number equals the last row number in workbbok, if so stop Loop
        If CurCell.Row = Rows.Count Then Exit Do

        'The SET statement allows you to save an object reference to a variable, in this case it is the last cell in column A.
        Set CurCell = CurCell.End(xlDown)
    Loop

        'Save an object reference to the next cell to the right based on variable c to variable CurCell
        Set CurCell = Range("A1").Offset(0, c)

'Continue with next number 
Next c

'Iterate through each value saved in collection variable Adr
For Each Value In Adr
    'Add value to string variable result using variable delch as a delimiting character
    result = result & delch & Value

'Continue with next value in collection
Next Value

'Add a new sheet and save a reference to variable sht
Set sht = Sheets.Add

'Save text in string result to cell A1
sht.Range("A1") = Right(result, Len(result) - 1)
End Sub

3.2 Where to put the code?

1. Copy above VBA code.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Select your workbook in the Project Explorer.
4. Press with left mouse button on "Insert" on the menu.
5. Press with left mouse button on "Module" to create a code module named Module1 that will be displayed below "Modules" in the Project Explorer.
6. Paste VBA code to the code window, see image above.
7. Return to Excel.

Note, save your workbook with file extension *.xlsm (macro-enabled) to attach the VBA code to the workbook.

3.3 How to use the macro

The animated image above shows how to run the macro.

1. Press Alt + F8 to open the macro dialog box.
2. Select ExtractAddresses.
3. Press with mouse on button "Run".
4. The macro asks for a delimiting character.
5. The macro creates a new worksheet and populates cell A1 with cell references containing values. These cell references have a comma as a delimiting character.

4. Locate a shape in a workbook

This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The image above shows a worksheet that allows you to specify a value in cell B1 and a button that runs a macro.

 

4.1. Locate a shape

The macro iterates through all worksheets in the workbook and then iterates through all shapes in each worksheet to find a shape with the given text value.

The macro then activates the worksheet containing that particular shape, selects the shape, and moves to the location of that shape.

How to use the workbook

1. Enter the number you want to look for in cell B1.
2. Press with left mouse button on "Find shape" button shown in the image above.
   
3. The macro finds the shape and goes to that worksheet and location.

Back to top

VBA Code

'Name macro
Sub FindShape()

'Dimension variables and declare data types
Dim sht As Worksheet
Dim shp As Shape

'Iterate through worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets

    'Iterate through shapes in each worksheet
    For Each shp In sht.Shapes

        'Check if shape text meets condition in cell B1
        If shp.TextFrame.Characters.Text = Worksheets("Sheet1").Range("B1").Value Then
   
            'Activate worksheet
            sht.Activate

            'Select shape
            shp.Select

            'Scroll to row containing shape
            ActiveWindow.ScrollRow = shp.TopLeftCell.Row

            'Scroll to column containing shape
            ActiveWindow.ScrollColumn = shp.TopLeftCell.Column

            'Exit macro
            Exit Sub
        End If
    Next shp
Next sht
End Sub

Back to top

Where to put the code?

If you want to use the macro in your workbook you need to follow these steps.

1. Copy above VBA code.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Double press with left mouse button on your workbook name (Locate a shape.xlsm) in the Project Explorer.
4. Press with left mouse button on "Insert" on the menu.
5. Press with left mouse button on "Module" to create a module in your workbook.
6. Paste VBA code to code window, see above image.

Back to top

4.2. Locate an Excel Table or a named range

To find a specific Excel Table in a Workbook is easy as long as you know the name of it. Type the name in the name box at top left corner of your Excel window, see image above.

This will take you instantly to the Excel Table even if it is located in another worksheet. If you don't know the name of the Excel Table I recommend you use the "Name  Manager" to find the Excel Table you are looking for.

1. Go to tab "Formulas" on the ribbon.
2. Press with mouse on "Name Manager" button.
   
3. The "Name Manager" shows the names of all Excel Tables and named ranges in your workbook along with the worksheet and cell references.

The VBA code below demonstrates how to locate an Excel Table or a named range programmatically.

'Name macro
Sub Macro1()

'Go to named range named "BBC", this will also work with cell references and Excel Tables
Application.Goto Reference:="BBC"
End Sub

Where to put the code?

Back to top

4.3. Locate a shape/chart by name

The following macro iterates through all shapes and charts in your workbook looking for the name "Chart 1". When found it goes to that particular worksheet and to the exact location of the shape or chart.

'Name macro
Sub FindChart()

'Dimension variables and data types
Dim sht As Worksheet
Dim shp As Shape

'Iterate through all worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets

    'Iterate through all shapes in worksheet
    For Each shp In sht.Shapes

        'Check if name is "Chart 1"
        If shp.Name = "Chart 1" Then

            'Activate worksheet
            sht.Activate

            'Select chart
            shp.Select

            'Go to row that matches the chart location
            ActiveWindow.ScrollRow = shp.TopLeftCell.Row

            'Go to column that matches the chart location
            ActiveWindow.ScrollColumn = shp.TopLeftCell.Column

            'End macro
            Exit Sub
        End If

    'Continue with next shape
    Next shp

'Continue with next worksheet
Next sht
End Sub

Where to put the code?

4.4. List all names of shapes and charts in a workbook

This macro creates a list in worksheet "List" of all shape and chart names in your workbook. It also lists their corresponding cell address and what worksheet they are on.

The list is created in column A and B in worksheet "List", you can change this location, if you prefer, in the macro below.

'Name macro
Sub ListShapes()

'Dimesnion variables and declare data types
Dim sht As Worksheet
Dim shp As Shape, r As Integer

'Save 0 (zero) to variable r
r = 0

'Iterate through each worksheet in the active workbook
For Each sht In ActiveWorkbook.Worksheets

    'Iterate through each shape
    For Each shp In sht.Shapes

        'Save shape name to column A based on variable r
        Worksheets("List").Range("A1").Offset(r, 0) = shp.Name

        'Save shape name to column B based on variable r
        Worksheets("List").Range("B1").Offset(r, 0) = sht.Name & "!" & shp.TopLeftCell.Address

        'Add 1 to the number saved in variable r
         r = r + 1

    'Continue with next shape
    Next shp
Next sht
End Sub

Where to put the code?

Back to top

5. Move a shape - VBA

This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape you want, using VBA code. Select a cell containing the button name, and the black arrow instantly points to the corresponding button.

What's on this page

1. How to change the location of a shape on worksheet
2. How to get the top and left value of a shape?
3. How to find out the width and height of a shape?
4. Get Excel file

5.1. How to change the location of a shape on worksheet

I made a simple picture from an old remote I found, see image above. The macro is actually event code placed in a worksheet module, I will describe this in detail.

The VBA code checks that the selected cell is in cell range F5:F13. If true, then it reads the coordinates on the same row. The shape is then moved to that location. Get the workbook and try it out.

The arrow is hidden if the selected cell is not one of the cells in cell range F5:F13.

5.1.1 VBA code

'Event code is started if a cell is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.Shapes.Range(Array("Straight Arrow Connector 42"))

    'Make sure the selected cell is in cell range F5:F13
    If Not Intersect(Target, Range("F5:F13")) Is Nothing Then

            'Make the shape visible
            .Visible = True

            'Change the number of points between the shape and the row numbers
            .Left = ActiveCell.Offset(0, 1)

            ''Change the number of points between the shape and the column letters
            .Top = ActiveCell.Offset(0, 2)
        Else

        'Hide the shape
        .Visible = False
    End If
End With
End Sub

Make sure you change the shape name if your shape has a different name. To check the shape name select it and read the name from the name box, see the image below.

You can find the name the top left corner "Straight Arrow Connector 42" which matches the name in the macro.

Back to top

5.1.2 Where to put the event code?

1. Press with right mouse button on on a worksheet tab at the bottom of the Excel window. Make sure it is the worksheet you want to use.
2. Press with mouse on "View Code".
   
3. Paste code to the worksheet module.
   
4. Exit VB Editor and return to Excel.

Back to top

5.1.3 Animated image

I created a small animated image that shows what happens when you press with left mouse button on each cell in cell range F5:F13, note that the shape is hidden if you press with left mouse button on a cell outside cell range F5:F13.

Back to top

5.2. How to get the top and left value of a shape?

The following macro shows a message box with the left and top points of shape "Straight Arrow Connector 42".

'Name macro
Sub Position()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.Shapes("Straight Arrow Connector 42")

'Show a message box containing top and left points
MsgBox "Left: " & .Left & " Top: " & .Top
End With
End Sub

5.2.1 Where to put the macro code?

1. Press Alt + F11 to open the Visual Basic Editor.
2. Press with mouse on "Insert" on the top menu.
3. Press with mouse on "Module".
4. Copy above VBA code and paste to code window.
5. Exit VB Editor and return to Excel.

Back to top

5.2.2 How to run the macro?

You can link assign the macro to the shape which will start the macro each time you press with left mouse button on shape. You can also press shortcut keys Alt + F8 to open the macro dialog box and start the macro from there.

1. Press with right mouse button on on shape "Straight Arrow Connector 42".
2. Press with mouse on "Assign macro".
3. A dialog box appears, press with left mouse button on the macro you want to assign.
   
4. Press with left mouse button on the OK button.

Back to top

5.3. How to find out the width and height of a shape?

The following macro displays a message box containing the height and width of shape "Straight Arrow Connector 42".

Sub Size()
With ActiveSheet.Shapes("Straight Arrow Connector 42")
MsgBox "Height: " & .Height & " Width: " & .Width
End With
End Sub

Put this macro in a module just like the other macro above, this is not event code.

Back to top

Back to top

6. Create links to all sheets in a workbook

The macro inserts hyperlinks to all worksheets in your workbook except the current worksheet.

6.1 Video

Watch a video where I explain how to use the macro

Back to top

6.2 VBA code

Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
    If ActiveSheet.Name <> sh.Name Then
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
        ActiveCell.Offset(1, 0).Select
    End If
Next sh
End Sub

Back to top

6.3 Explaining vba code

Sub CreateLinksToAllSheets()

All macros start with Sub and end with End Sub.

Dim sh As Worksheet

This line declares sh as a worksheet object.

Dim cell As Range

Cell is declared a range object.  A range object can contain a single cell or multiple cells.

For Each sh In ActiveWorkbook.Worksheets

Each worksheet in active workbook is stored in sh, one by one.

If ActiveSheet.Name <> sh.Name Then

This If ... then line avoids linking to active worksheet.

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sh.Name & "!A1", TextToDisplay:=sh.Name

Create a hyperlink to current worksheet sh in active cell.

ActiveCell.Offset(1, 0).Select

Select next cell below active cell.

End If

Next sh

Go back to the "For each" statement and store next worksheet in sh worksheet object.

End Sub

Stop macro
Back to top

6.4 Where to put the code?

1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Press with the right mouse button on your workbook name in the project explorer, see image above.
   A context menu appears.
3. Press "Insert" and another popup menu appears.
4. Press "Module" to insert a new module.
5. Copy and paste the VBA macro code above to the newly created code module in your workbook.

Back to top

6.5 How to run the macro

1. Select a cell where you want to begin inserting hyperlinks.
2. Press Alt + F8 to view macros.
3. Select CreateLinksToAllSheets and then press with left mouse button on the "Run" button.

Back to top

6.6 Get Excel file

Back to top

7. Create links to all visible worksheets in a workbook

The image above shows the output of a macro named CreateLinksToAllVisibleSheets. There are two hidden worksheets in this workbook: "hidden" and "Annual report 2011".

The macro returns only hyperlinks of visible worksheets in column A, see the image above.

'Name macro
Sub CreateLinksToAllVisibleSheets()

'Dimension variables and declare data types
Dim sh As Worksheet

'Go through all worksheets in the current workbook
For Each sh In ActiveWorkbook.Worksheets

    'Check if current worksheet name is not equal to the worksheet in variable sh AND is visible
    If ActiveSheet.Name <> sh.Name And sh.Visible = True Then

        'Create hyperlink in the selected cell
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" &  "!A1", TextToDisplay:=sh.Name

        'Select next cell below
        ActiveCell.Offset(1, 0).Select
    End If

'Continue with next worksheet
Next sh
End Sub

Where to put the code?

How to run the macro?

Back to top

7.1 Get Excel file

Back to top

8. Create links to all hidden worksheets in a workbook

The image above shows a workbook with two hidden worksheets "hidden" and "Annual report 2011". The macro created two hyperlinks in cells A2:A3.

The hyperlinks don't work, the worksheets are hidden and you can't navigate to those worksheets unless you unhide them.

'Name macro
Sub CreateLinksToAllHiddenSheets()

'Dimension variables and declare data types
Dim sh As Worksheet

'Go through all worksheets in the current workbook
For Each sh In ActiveWorkbook.Worksheets

    'Check if current worksheet name is not equal to the worksheet in variable sh AND is not visible
    If ActiveSheet.Name <> sh.Name And sh.Visible = False Then

        'Create hyperlink in the selected cell
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name

        'Select next cell below
        ActiveCell.Offset(1, 0).Select
    End If

'Continue with next worksheet
Next sh
End Sub

Where to put the code?

How to run the macro?

Back to top

8.1 Get Excel file

Back to top

Recommended articles

Recommended articles

Recommended articles

9. Creates links to all sheets, tables, pivot tables and named ranges

This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, Pivot tables, Excel defined Tables and named ranges.

The hyperlinks allow you to navigate quickly to any of the Excel objects mentioned above.

What you will learn in this article

• Iterate through worksheets in the active workbook.
• Iterate through Pivot Tables in the active worksheet.
• Iterate through Excel defined Tables in the active worksheet.
• Iterate through named ranges in the active workbook.
• Create hyperlinks programmatically.
• Change column width so the content fits.
• Save text value to a cell using VBA.
• Bold cell text programmatically.
• Select the next cell below using VBA.

How to use macro

The animated image above demonstrates the macro.

1. Select a destination cell on a worksheet you want to populate.
2. Go to tab "Developer" on the ribbon.
3. Press with left mouse button on the "Macros" button and the Macro dialog box appears.
4. Select macro CreateToC.
5. Press with left mouse button on "Run" button.
6. The macro creates hyperlinks to all worksheets, pivot tables, Excel defined Tables and named ranges in the active workbook.
7. The macro ends.

VBA code

'Name macro
Sub CreateToC()

'Dimension variables and declare data types
Dim sh As Worksheet
Dim cell As Range
Dim pt As PivotTable
Dim tbl As ListObject
Dim nms As Name

'Populate selected cell with "Table of Contents"
ActiveCell.Value = "Table of Contents"

'MAke the selected cell bolded
ActiveCell.Font.Bold = True

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Save text Worksheets to selected cell
ActiveCell.Value = "Worksheets"

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Iterate through each worksheet in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Make sure the worksheet name is not equal to the currently selected worksheet
    If ActiveSheet.Name <> sh.Name Then

        'Add hyperlink to selected cell with worksheet name linking the the worksheet
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name

        'Select the next cell below
        ActiveCell.Offset(1, 0).Select
    End If
Next sh

'Save text Pivot tables to selected cell
ActiveCell.Value = "Pivot tables"

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Iterate through all worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Iterate through all pivot tables in worksheet
    For Each pt In sh.PivotTables

        'Add hyperlink to selected cell with pivot table name linking to the pivot table
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'!" & pt.TableRange1.Address, TextToDisplay:=pt.Name

        'Select the next cell below
        ActiveCell.Offset(1, 0).Select

    'Continue with next pivot table
    Next pt

'Continue with next worksheet
Next sh

'Save text Tables to selected cell
ActiveCell.Value = "Tables"

'Select the next cell below
ActiveCell.Offset(1, 0).Select


'Iterate through all worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Iterate through all Excel defined Tables in worksheet
    For Each tbl In sh.ListObjects

            'Add hyperlink to selected cell with Excel defined Table name linking to the Excel defined Table
            ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            tbl.Name, TextToDisplay:=sh.Name

            'Select the next cell below
            ActiveCell.Offset(1, 0).Select

    'Continue with next Excel defined Table
    Next tbl

'Continue with next worksheet
Next sh

'Save text Named Ranges to selected cell
ActiveCell.Value = "Named ranges"

'Select the next cell below
ActiveCell.Offset(1, 0).Select

'Iterate through all named ranges in workbook
For Each nms In ActiveWorkbook.Names

            'Add hyperlink to selected cell with the name of the named range linking to the the named range
            ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            nms.Name, TextToDisplay:=nms.Name

            'Select the next cell below
            ActiveCell.Offset(1, 0).Select

'Continue with next named range
Next nms

'Change column widths so they fit the content
ActiveSheet.Columns(ActiveCell.Column).AutoFit

End Sub

Where to put the code?

1. Copy above VBA code.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Select your workbook in the Project Explorer.
4. Press with left mouse button on "Insert" on the menu.
5. Press with left mouse button on "Module" to insert a code module.
6. Paste VBA code to the code module.
7. Return to Excel.

Hyperlinks category

Worksheet category

More than 1300 Excel formulas

Excel categories

Comments (78)