Archive for CommandBarControl

Add Control to CommandBars in WorksheetMenuBar

December 21, 2008 at 23:41 · Filed under Code Snippets, CommandBarControl ·Tagged

Sub AddControlToWorksheetMenuBar(ByRef NewCmdControl As String, _
              ByRef cmdBar As String, ByRef Face_ID As Long, _
              ByRef On_Action_Proc As String, _
              Optional ByRef Begin_Group As Boolean = False)
  ‘   Created by Selva V Pasupathy, Hyderabad
  ‘ This custom procedure adds a button control to commandbar
  ‘ Can be used to add a control to one of the worksheet menu bar
  ‘ where NewCmdControl = Caption for new CommandBarControl
  ‘ & NewCmdControl will be the name to that control
  ‘ Face_ID = one of the long integers for face_id
  
  Dim newItem As CommandBarControl

  With CommandBars(cmdBar)
    
    With .Controls(NewCmdControl)
      On Error Resume Next
      .Delete
      On Error GoTo 0
    End With
    
    Set newItem = .Controls.Add(Type:=msoControlButton)
    
    With newItem
      .BeginGroup = Begin_Group
      .Caption = NewCmdControl
      .FaceId = Face_ID
      .OnAction = “qtrReport”
    End With
  End With

End Sub

Leave a Comment

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