Dynamic lists in Excel Combo Boxes

Hi Stephen,

hope all this makes sense.

Re: 1) Yes, the combo box will need to pick from 2 different columns depending on user selection. I know how to do this, but currently have a 'static' list that must be maintained in order to generate the drop down.

Re: 2) no, just a list of names, so single column, single piece of info

Re: 3) When the record is selected, this will populate a variable which allows a piece of code to effectively copy and paste data matching the criteria set by the combo box

essentially, I want to get away from having to maintain a list of names seperately to the data in the main list. As data comes through for new people (or is not present for 'old' people) I'd like the combo box to only show a list of people that there is data for.

My combo-box code looks like this:

If Me.cboReportType.Value = "Select a Report...." Then Exit Sub

If Me.cboReportType.Value = "Sales Person Report" Then

For IntRowCount = 2 To Sheets("List Page").Range("A1").CurrentRegion.Rows.Count

Me.cboSelection.AddItem Sheets("List Page").Cells(IntRowCount, 1).Value

Next IntRowCount

Else

For IntRowCount = 2 To Sheets("List Page").Range("C1").CurrentRegion.Rows.Count

Me.cboSelection.AddItem Sheets("List Page").Cells(IntRowCount, 3).Value

Next IntRowCount


End If

If I tell that to look at the data page, I'm not sure that it will give me a distinct list of people (rather than listing each person multiple times for multiple rows of data).