Add Sheet Navigation control on ToolBar

Selva V Pasupathy,   HSBC Global Resourcing, Hyderabad

Add the following code to a code module of excel workbook and run macro named “AddComboNavigation”. This will add a combobox control on standard toolbar on microsoft excel. This will make sure that you will be able to navigate between sheets, especially wehn you have a workbook with 100’s of sheets. This is one think that I would like to use and dont have problem even creating workbooks with 100s of worksheets.

Option Explicit
Sub AddComboNavigation()
  Dim cBar As CommandBar
  Dim c As CommandBarComboBox
  Dim i As Integer
  ‘ Set reference to standard toolbar
  Set cBar = Application.CommandBars(“standard”)
  cBar.Reset
  
  ‘Add Combobox Control
  Set c = cBar.Controls.Add(msoControlComboBox, 1)
  
  With c
    .Clear
    For i = 1 To ThisWorkbook.Sheets.Count
      .AddItem ThisWorkbook.Sheets(i).Name, 1
    Next i
      .Caption = “Sheet Navigator”
      .DescriptionText = “This is the area where you can place description area”
      .Enabled = True
      .Visible = True
      .DropDownLines = 5
      .ListIndex = 0
      .OnAction = “Activate_Sheet”
  End With
End Sub

Private Sub Activate_Sheet()
  ‘on error resume next
  Dim x As String
  Dim c As CommandBarComboBox
  
  Set c = Application.CommandBars(“standard”).Controls(“Sheet Navigator”)
  If c.ListIndex 0 Then
  Sheets(c.ListCount – c.ListIndex + 1).Activate
  End If
End Sub

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: