Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Navigate Between Worksheets by Using Drop Down List in Excel?
Excel is an effective spreadsheet programme that enables you to efficiently organise and analyse data. The foundation of Excel are worksheets, therefore being able to swiftly switch between them will significantly increase your productivity. In this article, we'll concentrate on navigating between worksheets with ease by using a drop-down list as a navigational tool. You'll discover how to design a user-friendly drop-down menu that offers a quick method to access various worksheets in your workbook rather than having to wade through a big number of tabs at the bottom of your Excel window.
You will have a firm grasp on how to create an Excel drop-down list and effectively use it to transition between worksheets by the end of this tutorial. So let's get started and learn more about this time-saving method together!
Navigate Between Worksheets by Using Drop Down List
Here we will first insert a Combo box, then add VBA code to it to complete the task. So let us see a simple process to know how you can navigate between worksheets by using a drop-down list in Excel.
Step 1
Consider any Excel workbook where you have multiple worksheets.
First, click on developer and then on insert, then click on the combo box under ActiveX controls.
Developer > Insert > Combo Box.
Step 2
Then draw it. Now right-click on the box and select View Code. Copy the below code into the text box.
Draw > Right Click > View Code > Copy.
Code
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub
Private Sub ComboBox1_DropButtonClick()
Dim xSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
If ComboBox1.ListCount <> ThisWorkbook.Sheets.Count Then
ComboBox1.Clear
For Each xSheet In ThisWorkbook.Sheets
ComboBox1.AddItem xSheet.Name
Next xSheet
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub ComboBox1_GotFocus()
If ComboBox1.ListCount <> 0 Then ComboBox1.DropDown
End Sub
Step 3
Then close the VBA using Alt + Q then exit the Design mode. From now on, when a sheet is selected from the list, it will be opened.
This is how you can navigate between worksheets by using a drop-down list in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can navigate between worksheets by using a drop-down list in Excel to highlight a particular set of data.
152 Views
