ComboBox and multi sheets
 

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

ComboBox and multi sheets
 

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

ComboBox and multi sheets
 

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." wrote in message
...
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

ComboBox and multi sheets
 

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

ComboBox and multi sheets
 

IC, Tnx

"Jim Thomlinson" wrote in message
...
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

ComboBox and multi sheets
 

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


"JLGWhiz" wrote in message
...
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." wrote in message
...
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

ComboBox and multi sheets
 

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" wrote in message
...
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