Navigate Between Sheets of the Workbook With Drop Down List On Add Ins Tab

Excel Add Ins Tab

           In this template, we created the Add-ins tab and added a drop down list on  this tab to navigate between the pages of the workbook .

VBA codes to add ins tab and to create drop down list :
Dim sht As Worksheet
On Error Resume Next
On Error GoTo 0
With Application.CommandBars.Add("Sheet Navigate", , False, True)
With .Controls.Add(msoControlDropdown)
For Each sht In ActiveWorkbook.Sheets
If sht.Visible = xlSheetVisible Then
.AddItem "" & sht.Name
.ListIndex = 1
End If
Next sht
.TooltipText = "SheetNavigate"
.OnAction = "Sheet_Navigate"
End With
.Protection = msoBarNoCustomize
.Position = msoBarTop
.Visible = True
End With
End Sub

Private Sub Sheet_Navigate()
'navigate to selected sheet
Dim stActiveSheet As String
With CommandBars.ActionControl
stActiveSheet = .List(.ListIndex)
End With
Worksheets("" & stActiveSheet).Activate
End Sub

No comments:

Post a Comment