the following is code to include a combo box in a menu bar. I'm sure you
could adapt it.
The code is called from the first form to open up:
You need to remember to set a reference to the Microsoft Office object
libarary (MO9.DLL or whatever version of office you have on your PC)
In the following example all the Groups that whoever has logged in belong to
are listed in a combo box. The values to be used in the dropdown are taken
from a table:
Public Sub setupPositiongroups()
Dim mybar As CommandBar
Dim mycontrol As CommandBarComboBox
Dim myrs As Recordset
Set myrs = CurrentDb().OpenRecordset("Select * from Groups where [Owner]='"
& gcurrentuser & "' AND [JOB]=TRUE order by [groupname];", dbOpenSnapshot)
'see the FindControl Method in the VBA Help to get a list of constants you
can use for TYPE
Set mycontrol = CommandBars.FindControl _
(TYPE:=msoControlComboBox, Tag:="Position Groups", Visible:=True)
'be sure to delete any previous menu control or else you will end up with a
whole menu full of listboxes
If mycontrol Is Nothing Then
Else
mycontrol.delete
End If
'Replace CasMax3 with the name of your own menu bar
Set mybar = CommandBars("CasMax3")
Set mycontrol = mybar.Controls.Add(TYPE:=msoControlComboBox, ID:=1)
'the first item 'None Selected' in manually added. If you wanted to insert
a value list such as Monday, Tuesday, Wednesday etc. this is how you would
do it.
'otherwise loop through a recordset
With mycontrol
.AddItem "None Selected", 1
Do While Not myrs.EOF
.AddItem myrs!GroupName
myrs.MoveNext
Loop
.Style = msoComboLabel
.Caption = " Active Position Group"
.DropDownLines = 3
.DropDownWidth = 75
.ListHeaderCount = 0
'the tag is important as it is used later to identify which menu control has
been used
.Tag = "Position Groups"
.Parameter = "Position"
'the On Action value refers to a macro name which in turn calls the
SelectPositionGroup function (see below)
.OnAction = "PositionGroupNames"
End With
gActivePGroup = ""
Set mycontrol = Nothing
Set mybar = Nothing
Set myrs = Nothing
End Sub
'*********************************************
Public Function SelectPositionGroup()
'this function is called from a macro named PositionGroupNames (set on the
OnAction property of the menu control) see below
Dim mycontrol As CommandBarComboBox
'The control is identified by it's tag name
Set mycontrol = CommandBars.FindControl _
(TYPE:=msoControlComboBox, Tag:="Position Groups", Visible:=True)
If mycontrol Is Nothing Then
Else
If mycontrol.Text = "None Selected" Then
gActivePGroup = ""
Else
gActivePGroup = mycontrol.Text
End If
End If
End Function
'********************************************
'Set up a macro with the following properties
Macro Name: PositionGroupNames
Macro Action: RunCode
Function Name: SelectPositionGroup()
'********************************************
Hope this helps you
June Macleod
