New group button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I have the following code to create a new toolbar and add a button

Sub StaffSalariedbar(
Dim NewBtn As CommandBarContro
Dim StaffSalariedTBar As CommandBa

On Error Resume Nex
CommandBars("Salaries Toolbar").Delet
On Error GoTo
Set StaffSalariedTBar = CommandBars.Ad
With StaffSalariedTBa
.Name = "Salaries Toolbar
.Left = 105
.Top = 15
.Visible = Tru
End Wit

Set NewBtn = CommandBars("Salaries Toolbar").Controls.Add
(Type:=msoControlButton
With NewBt
.BeginGroup = Tru
.Style = msoButtonIconAndCaptio
.FaceId = 65
.OnAction = "ShowMenu
.Caption = "Main Menu
End Wit

End Su

How do I make the button the begining of a group (i.e it has that little drop down box where you can select more buttons) and then add buttons to that group.

Thanks for any hel
 
Setting the BeginGroup property to True adds a vertical
separator line between that button and previous buttons on
a tool bar. It doesn't create a drop down type control.

To do what you want, you have to add a control of Type 10
to the commandbar and then add controls to it. Also note
that it you are creating the control with the
Workbook_Open event, if you set its Temporary property to
True, then you won't have to delete it because it will be
destroyed upon closing Excel.

The following code is a bit more complex than what you are
doing. However, typically one would be adding several
buttons -- each with a caption, faceId and macro
assignment. As such, the code can get rather long. I
only show the additon of 3 buttons to the drop down
control. This technique would be used if there were
several buttons to add.

Sub StaffSalaryBar
Dim SalTB As CommandBar
Dim Ctrl As CommandBarControl
Dim NewBtn As CommandBarControl
Dim MacroArr As Variant
Dim CapArr As Variant, FaceIdArr As Variant
Dim M1 As String, M2 As String, M3 As String
Dim Cap1 As String, Cap2 As String, Cap3 As String
Dim F1 As Integer, F2 As Integer, F3 As Integer

'Three macros with these names assumed to exist
M1 = "EmpSalaryCurrMonth"
M2 = "EmpSalaryYTD"
M3 = "EmpInfo"
'Specify captions for each button
Cap1 = "Current"
Cap2 = "Year to date"
Cap3 = "Info"
'Specify FaceId's for buttons
F1 = 650
F2 = 100
F3 = 101
'Package macros, tooltips, captions & FaceIds
MacroArr = Array(M1, M2, M3)
CapArr = Array(Cap1, Cap2, Cap3)
FaceIdArr = Array(F1, F2, F3)
'The following 3 lines are not necessary if called
'on Workbook_Open because tool bar is Temporary
On Error Resume Next
Application.CommandBars("Salaries").Delete
On Error GoTo 0

Set SalTB = Application.CommandBars.Add _
(Name:="Salaries", Temporary:=True)
With SalTB
.Left = 100
.Top = 150
.Visible = True
End With

Set Ctrl = SalTB.Controls.Add(Type:=10)
Ctrl.Caption = "Employee"
Ctrl.TooltipText = "Employee information macros"
For i = 0 To UBound(MacroArr)
Set NewBtn = Ctrl.Controls.Add _
(Type:=msoControlButton)
With NewBtn
.Style = msoButtonIconAndCaption
.OnAction = MacroArr(i)
.Caption = CapArr(i)
.FaceId = FaceIdArr(i)
End With
Next
End Sub

Regards,
Greg
 
This is brilliant
Thanks a million Greg

Roj

----- Greg Wilson wrote: ----

Setting the BeginGroup property to True adds a vertical
separator line between that button and previous buttons on
a tool bar. It doesn't create a drop down type control.

To do what you want, you have to add a control of Type 10
to the commandbar and then add controls to it. Also note
that it you are creating the control with the
Workbook_Open event, if you set its Temporary property to
True, then you won't have to delete it because it will be
destroyed upon closing Excel

The following code is a bit more complex than what you are
doing. However, typically one would be adding several
buttons -- each with a caption, faceId and macro
assignment. As such, the code can get rather long. I
only show the additon of 3 buttons to the drop down
control. This technique would be used if there were
several buttons to add

Sub StaffSalaryBa
Dim SalTB As CommandBa
Dim Ctrl As CommandBarContro
Dim NewBtn As CommandBarContro
Dim MacroArr As Varian
Dim CapArr As Variant, FaceIdArr As Varian
Dim M1 As String, M2 As String, M3 As Strin
Dim Cap1 As String, Cap2 As String, Cap3 As Strin
Dim F1 As Integer, F2 As Integer, F3 As Intege

'Three macros with these names assumed to exis
M1 = "EmpSalaryCurrMonth
M2 = "EmpSalaryYTD
M3 = "EmpInfo
'Specify captions for each butto
Cap1 = "Current
Cap2 = "Year to date
Cap3 = "Info
'Specify FaceId's for button
F1 = 65
F2 = 10
F3 = 10
'Package macros, tooltips, captions & FaceId
MacroArr = Array(M1, M2, M3
CapArr = Array(Cap1, Cap2, Cap3
FaceIdArr = Array(F1, F2, F3
'The following 3 lines are not necessary if calle
'on Workbook_Open because tool bar is Temporar
On Error Resume Nex
Application.CommandBars("Salaries").Delet
On Error GoTo

Set SalTB = Application.CommandBars.Add
(Name:="Salaries", Temporary:=True
With SalT
.Left = 10
.Top = 15
.Visible = Tru
End Wit

Set Ctrl = SalTB.Controls.Add(Type:=10
Ctrl.Caption = "Employee
Ctrl.TooltipText = "Employee information macros
For i = 0 To UBound(MacroArr
Set NewBtn = Ctrl.Controls.Add
(Type:=msoControlButton
With NewBt
.Style = msoButtonIconAndCaptio
.OnAction = MacroArr(i
.Caption = CapArr(i
.FaceId = FaceIdArr(i
End Wit
Nex
End Su

Regards
Gre
 
Back
Top