Combo Boxes

[Johnny]

I've search throught related discussions and cannot find quite what I'm
after. I've created a drop down form field with three Department Names.
Let's say they are Dept's A, B and C. Based on the seletion I would like
combobox1 to load with the corresponding list of employees who are maintained
on an Excel spreadsheet. Column A of the spreadsheet contains the employees
for Dept A, Column B contains a list of employee for Dept B and so on. Thus,
when the user select Debt A in the drop down box, the combobox1 would
popluate with the employees for Dept A from the Excel spreadsheet name
EmployeeList.xls.

Any help would be much appreciated.

 

[Greg Maxey]

Johnny,

I don't know if this is going to be of much help because I am not sure
we are talking in the same terms. You mention drop down form field
and combobox. AFAIK comboboxes don't exist in Word protected forms.
Also a drop down list in a protected form can only contain 25
entries. If you are using a dropdown list in a protected form for
DEPT A B and C and you want a dropdown list populated with the
employee names from a spreadsheet you could use something like this:

Sub OnExitDDList()
'You need to set a reference in your project to the
'"Microsoft DAO 3.51 (or 3.6) Object Library".
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim j As Long
'Open a spreadsheet to retrieve data
Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
i = 0
'Loop through each recordset.
ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries.Clear
While Not rs.EOF
Select Case ActiveDocument.FormFields("Dropdown1").Result
Case "A"
ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries.Add
rs.Fields(0).Value
Case "B"
ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries.Add
rs.Fields(1).Value
Case "C"
ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries.Add
rs.Fields(2).Value
End Select
rs.MoveNext
i = i + 1
Wend
'Clean up.
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

The first row of the spreadsheet contains the labels A B and C

 

[Johnny]

Greg, thanks for the quick reply. After reading through my question, I see
that I misstated my case. Sorry about that! Let me try again.

I created a userform with a combo box that is hard coded in the VBA code to
popluate with an array when the form initializes. It looks like this:

Private Sub UserForm_Initialize()
ComboBox1.ColumnCount = 1
ComboBox1.List() = Array("Employee 1", "Employee 2", "Employee 3", "etc")
End Sub

The userform loads when the use clicks on the form field
Once the uses selects the employee form the userform, the code to poluate
the text field on the form looks like this:

Private Sub ComboBox1_Change()
ActiveDocument.FormFields(1).Result = ComboBox1.Value
End Sub


Right now, the array only contains the names for one Dept. which has 28
employees. Instead of entering the employees for the other two departments.
I just create copies of the document for those departments and changed the
names.

So, what I would like is to have a drop down list on the form where the user
selects the department (A,B, or C) and combo box on the userform would be
populated with the corresponding employees for that department.

The code you gave me below to retrieve the data from Excel seems a little to
complex for my level of knowledge. So, if you would, could you provide me
with a means to hard code the employees in VBA.

 

[Greg Maxey]

Johnny,

Ok. I see what you wanted to do. The spreasheet is simple a 3 column
list of employee names. Select the entire range of names and use the
Insert>Name>Define menu and name it mySSRange.

I have saved my spreadsheet as C:\Book1.xls just change the name in
the code below as appropriate.

I also used a command button rather than the combobox_change event to
write the selection to the text formfield.

Option Explicit
Private Sub CommandButton1_Click()
ActiveDocument.FormFields("Text1").Result = Me.ComboBox1.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
'You need to set a reference in your project to the
'"Microsoft DAO 3.51 (or 3.6) Object Library".
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
'Open a spreadsheet to retrieve data
Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
i = 0
'Loop through each recordset.
Me.ComboBox1.Clear
While Not rs.EOF
Select Case ActiveDocument.FormFields("Dropdown1").Result
Case "A"
Me.ComboBox1.AddItem rs.Fields(0).Value
Case "B"
Me.ComboBox1.AddItem rs.Fields(1).Value
Case "C"
Me.ComboBox1.AddItem rs.Fields(1).Value
End Select
rs.MoveNext
i = i + 1
Wend
'Clean up.
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

If you want to stay away from the excel sheet, you could hard code as
follows:
Private Sub UserForm_Initialize()
Me.ComboBox1.Clear
Select Case ActiveDocument.FormFields("Dropdown1").Result
Case "A"
Me.ComboBox1.List = Split("Mary Jones,Joe Miller, Bill
Bradly, ...", ",")
Case "B"
Me.ComboBox1.List = Split("John Smith,Bob Jenkins, Nancy
Robinson, ...", ",")
Case "C"
Me.ComboBox1.List = Split("Emp1,Emp2,Emp3", ",")
End Select
End Sub