Archive for Code Snippets

Add Control to CommandBars in WorksheetMenuBar

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
      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