ComboBox and multi sheets

  • Thread starter Thread starter E.Z.
  • Start date Start date
E

E.Z.

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.
 
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.
 
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
 
IC, Tnx

Jim Thomlinson said:
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.
Click to expand...
 
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.
 
Back
Top