Try the following code to place a drop-down box on a toolbar that you can
hook into a macro (have the macro look to see what the value of the listbox
is, and use that in your processing). I commented out the extra code for
adding toolbar buttons, but you are welcome to adapt that if you need.

Public Const ToolbarName = "Test Toolbar"

Sub CreateToolbar()
Dim TBar As CommandBar
Dim NewDD As CommandBarControl
Dim NewBtn As CommandBarButton
Dim BeginThisGroup As Boolean

BeginThisGroup = False

'delete any previous old copy of the toolbar
On Error Resume Next
CommandBars(ToolbarName).Delete
On Error GoTo 0

'identify the starting position for placing the toolbar
OldToolBarTop = 0
For Each ctlCBarControl In Application.CommandBars
NewToolBarTop = ctlCBarControl.Top + ctlCBarControl.Height
If NewToolBarTop OldToolBarTop Then OldToolBarTop = NewToolBarTop
Next

'define the Toolbar
Set TBar = CommandBars.Add
With TBar
..Name = ToolbarName
..Visible = True
..Position = 1
..Top = OldToolBarTop
End With

'TMacros = Array("Macro1", _
' "Macro2", _
' "Macro3")
'TToolTip = Array("Tooltip 1", _
' "Tooltip 2", _
' "Tooltip 3")
'TIcon = Array("Icon1", _
' "Icon2", _
' "Icon3")
'TMask = Array("mask1", _
' "mask2", _
' "mask3")
'
'ShowOrder = Array(1, 2, 3)
'
'For p = LBound(ShowOrder) To UBound(ShowOrder)
'If p = 2 Then BeginThisGroup = True Else BeginThisGroup = False
' i = ShowOrder(p)
' AddAButton TBar, TMacros(i), TToolTip(i), TIcon(i), TMask(i),
BeginThisGroup
'Next

With TBar
Set NewDD = .Controls.Add(Type:=msoControlComboBox, ID:=1)
With NewDD
.Caption = "I am caption"
.Style = msoComboNormal
.AddItem "* ALL *", 1
.AddItem "Option1", 2
.AddItem "Option2", 3
.AddItem "Option3", 4
.ListIndex = 1
.OnAction = "Macro4"
End With
End With

End Sub

Sub AddAButton(ByVal TBar As CommandBar, ByVal MacroName As String, ByVal
uToolTip As String, ByVal uShape As String, ByVal uMask As String, BTG As
Boolean)
'AddAButton(CommandBar, MacroName, ToolTip, Icon, Mask, separatorbar)

Set NewBtn = TBar.Controls.Add(Type:=msoControlButton)
With NewBtn
..BeginGroup = BTG
..OnAction = MacroName
..Style = msoButtonIcon
SetIcon NewBtn, Sheet17.Shapes(uShape), Sheet17.Shapes(uMask)
..TooltipText = uToolTip
..Visible = True
End With

End Sub


"E.Z." wrote:

Hi,
I have a workbook with many worksheets.
In each worksheet I'm using a ComboBox control with some values.
My question is - Should I define a ComboBox for each sheet or is there a way
to define one ComboBox and use it every time for the ActiveSheet?

Tnx