Re: Combo Boxes by Greg
Greg
Thu Mar 22 11:28:25 CDT 2007
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
On Mar 22, 9:23 am, Johnny <Joh...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
>
> > On Mar 22, 7:16 am, Johnny <Joh...@discussions.microsoft.com> wrote:
> > > 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.- Hide quoted text -
>
> - Show quoted text -