Navigating Sheet in Microsoft Excel

Insert the following code in “This WorkBook” code module

 

Option Explicit
Dim i As Integer
Dim ShtNav, CmbNavigation As Name

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Call Del_Cmb
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
If Application.CommandBars("ShtNav").Visible = True Then
Application.CommandBars("ShtNav").Visible = False
End If

 

 

If Application.CommandBars("ShtNav").Controls("cmbnavigation").Visible = True Then
Application.CommandBars("ShtNav").Controls("cmbnavigation").Visible = False
End If

 

End Sub

Private Sub Workbook_Activate()
On Error Resume Next
If Application.CommandBars("ShtNav").Visible = False Then
Application.CommandBars("ShtNav").Visible = True
End If

If Application.CommandBars("ShtNav").Controls("cmbnavigation").Visible = False Then
Application.CommandBars("ShtNav").Controls("cmbnavigation").Visible = True
End If

CommandBars("shtnav").Left = Application.ActiveWindow.Width - 25
CommandBars("shtnav").Top = Application.ActiveWindow.Top + 25

End Sub

Private Sub Workbook_Open()
On Error Resume Next
Call Add_Cmb_in_Commandbar
End Sub

Sub Add_Cmb_in_Commandbar()
On Error Resume Next
Dim myBar As CommandBar
Dim myControl As CommandBarControl

Set myBar = Application.CommandBars.Add("ShtNav")
myBar.Position = msoBarBottom

Set myControl = myBar.Controls.Add(Type:=msoControlComboBox, ID:=1)

With myControl
For i = 1 To ThisWorkbook.Sheets.Count
.AddItem Text:=ThisWorkbook.Sheets(i).Name, Index:=i
Next i
.Visible = True
.Caption = "cmbnavigation"
.DropDownLines = 4
.DropDownWidth = 100
.ListHeaderCount = 0
.OnAction = "ProcessSelection"
End With
End Sub

Sub Del_Cmb()
On Error Resume Next
Application.CommandBars("ShtNav").Controls("cmbnavigation").Delete
Application.CommandBars("shtnav").Delete
End Sub

Enter the following code in Module1 code window

Sub ProcessSelection()
    On Error Resume Next
    Dim userChoice As Long
    userChoice = Application.CommandBars("ShtNav").Controls("CmbNavigation").ListIndex
    ThisWorkbook.Sheets(userChoice).Select
End Sub

To Download an example worksheet CLICK HERE