Kevin has attended:
Excel VBA Intro Intermediate course
Arrays as the source for a dropdown list
Hi
This is a just a general query (and probably very straight forward!) at this stage but is it possible to use an array as the source for the contents of a dropdown list and what might the code look like?
Thanks. Kev
RE: Arrays as the source for a dropdown list
Hi Kev
Thanks for getting in touch. Just before I answer that when you use the word "array", are you referring to VBA variable arrays, or another word for a range of cells?
It could have very different answers. I only ask because you've been on the Intro Intermediate course where we don't cover arrays.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Arrays as the source for a dropdown list
Hi Gary
Sorry, range of cells. I was discussing something with a colleague while typing the post! The bigger picture here is:
I want to create a list of people who have indicated their availability on a particular date so they can be allocated, via a dropdown, to one of five locations. If they have already been allocated to a location on that date I still want them to appear in the dropdown for the other four locations but not available for selection.
Thanks. Kev
RE: Arrays as the source for a dropdown list
Hi Kev
Thanks for getting in touch. To put the contents of some cells into a VBA combo box, click on the combo box and look for the RowSource property. You can enter the range or range name in there.
You can also use the Transpose and AddItem methods too, such as in this example:
http://stackoverflow.com/questions/13115034/excel-vba-combobox
To then make them unavailable for selection, you might want to perform validation on what has been selected, e.g. "If ComboBox1 = "Dave" Then MsgBox "Not available on this date"
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
