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

Objects like combo boxes are embeded in sheets so generally speaking you are
going to be better off to create one in each sheet. You can not create a
combo box that floats.
--
HTH...

Jim Thomlinson


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

If you want to use a combobox on multiple sheets, then put it on a UserForm
and call the UserForm when you need the Combobox.

When you attach the Combobox to the sheet, it becomes a child of the sheet.
i.e. x = Sheets(1).ComboBox1.Value
And it is only visible on that sheet.


"E.Z." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

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

IC, Tnx

"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:A5D96EC1-2335-4288-8306-(E-Mail Removed)...
> Objects like combo boxes are embeded in sheets so generally speaking you
> are
> going to be better off to create one in each sheet. You can not create a
> combo box that floats.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "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
>>
>>
>>

Tnx,
I didn't want to use a UserFrom 'cause it will show the dialog frame as
well.


"JLGWhiz" <(E-Mail Removed)> wrote in message
news:u3%(E-Mail Removed)...
> If you want to use a combobox on multiple sheets, then put it on a
> UserForm and call the UserForm when you need the Combobox.
>
> When you attach the Combobox to the sheet, it becomes a child of the
> sheet. i.e. x = Sheets(1).ComboBox1.Value
> And it is only visible on that sheet.
>
>
> "E.Z." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>
>

Tnx for the useful code,
I tested it and found out that I have no much control on such a ListBox. For
ex. - only Change event is available, no RTL alignment etc.
Anyway, was very useful to learn it.

"ker_01" <(E-Mail Removed)> wrote in message
news:AF6BB864-0808-47EC-A3C3-(E-Mail Removed)...
> 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
>>
>>
>>