Supposing, you have a workbook which contains multiple worksheets, now, you need to create a drop down list or combo box which lists all sheet names and when you select one sheet name from the drop down list, it will jump to that sheet immediately. This article, I will introduce how to navigate between worksheets by using a drop down list in Excel.
Navigate between worksheets by using drop down list in Excel
To jump to each selected sheet by using a drop down list, please do with the following steps:
1. Click Developer > Insert > Combo Box (ActiveX Control), see screenshot:
2. Then drag the mouse to draw a combo box, and then right-click it, select View Code from the context menu, see screenshot:
3. In the opened Microsoft Visual Basic for Applications window, copy and paste the following code to replace the original code, see screenshot:
VBA code: Navigate between worksheets by using drop down list
Private Sub ComboBox1_Change()
'Updateby Extendoffice
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
4. Then save and close this code window, and then click Design Mode to turn off the design mode, now, when selecting one sheet name from the combo box, it will go to that sheet at once, see screenshot:
Navigate between worksheets by using hyperlinks or buttons: With Kutools for Excel's Create List of Sheet Names utility, you can quickly list all worksheet names in a new sheet with hyperlinks or macro buttons, then you can navigate between them to your need.
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now! |
