How To Create A Drop Down List With Hyperlinks To Every Sheet In Excel?

In Excel, it can be useful to create a dropdown list that allows users to quickly navigate to different sheets within a workbook. One way to accomplish this is to create a drop down list with hyperlinks to every sheet in the workbook using VBA code.

In this tutorial, you will learn step-by-step how to create a macro that will create a dropdown list in a specified cell with the names of every sheet in the active workbook. The macro will also create a hyperlink in each cell next to the dropdown list that links to the corresponding sheet. By following this tutorial, you will be able to create a more user-friendly and efficient way to navigate your Excel workbook, saving you time and effort. So let's get started!

Create A Drop Down List With Hyperlinks To Every Sheet

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to see how you can create a drop-down list with hyperlinks to every sheet in Excel.

Step 1

Consider any workbook that contains multiple work sheets.

First, right-click on the sheet name and select View Code to open a VBA application. Then, click on Insert and select Module, then copy the below-mentioned text into the text box similar to the below image.

Right click > View code > Insert > Module >Copy code

Code

Sub CreateSheetLinksDropdown()
   Dim ws As Worksheet
   Dim list() As String
   Dim i As Integer
    
   ReDim list(1 To ActiveWorkbook.Worksheets.Count)
    
   i = 1
    
   For Each ws In ActiveWorkbook.Worksheets
      list(i) = ws.Name
         i = i + 1
   Next ws
   Range("A1").Validation.Delete
   Range("A1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(list, ",")
   For i = 1 To UBound(list)
      Range("A1").Hyperlinks.Add Anchor:=Range("B" & i), Address:="", SubAddress:="'" & list(i) & "'!A1", TextToDisplay:="Go to " & list(i)
   Next i
End Sub

In the code, A1 is the cell where the drop-down list will be created.

Step 2

Now save the sheet as a macro-enabled template and click F5 in the VBA application, and the drop-down list will be created successfully.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create a drop-down list with hyperlinks to every sheet in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 12-Jul-2023

242 Views

Kickstart Your Career

Get certified by completing the course

Get Started