Archive for CommandBar

Add Sheet Navigation control on ToolBar

November 9, 2008 at 14:45 · Filed under CommandBar, CommandBarCombobox, CommandBarControl, SheetNavigation, VBA Examples

- 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

Leave a Comment