Gio has attended:
Excel VBA Intro Intermediate course
Dynamic lists in Excel Combo Boxes
Hi,
How would I create a distinct, but dynamic list in an excel combo box. For example, if I had a list of sales data by person and I wanted to select 1 persons name, (without having to manually create a list) in a combobox?
RE: Dynamic lists in Excel Combo Boxes
Hi Gio
Thank you for your question
Could you possibly clarify a few points for me please?
1. Do you wish your combo box to show information from different lists, dependent on user entry
2. Do you wish your combo box to have multiple columns and thus show multiple pieces of information
3. What in general do you wnat to happen when a record is selected?
Thanks
Stephen
RE: 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).
RE: Dynamic lists in Excel Combo Boxes
Hi Gio
Thanks for youir question
I apologise for the delay in replying. I have been away from work for some time due to illness. I realise that you may have already solved the problem, if this is not the case could you please advise me so that I can giver it my urgent attention
Regards
Stephen
RE: Dynamic lists in Excel Combo Boxes
Hi Stephen,
Glad to hear you are on the mend. Not a problem on the delay, I've not actually found another way around it as such, other than manually tweaking the code as an when needed. I've not had as much time as I'd have liked to look at it in greater detail.
Is it possible to insert rows within the range of cells (like you can do with Sum/Count formulae) or is it better to use the CurrentRegion statement to select the list?
Thanks,
G
RE: Dynamic lists in Excel Combo Boxes
Hi Gio
Once again, apologies for the delay. My health issues were not as resolved as I had thought
Inserting rows within the middle of a named range will indeed work. However, I would always use the currentregion object to select a list. It is generally quicker to use this, as inserting rows changes the structure of a workbook and is therefore heavier on your PCs resources.
Regards
Stephen
