Excel VBA - Create Worksheet Dropdown List?

[DAmoss]

Can anyone give me an example of how to create a worksheet Dropdown List in Excel VBA which can be attached to a range of worksheet cells.

I'm interested in seeing the difference in the file size as compared to creating them using the usual Data / Validation / List method.

A simple day list would be fine as example:

Monday, Tuesday, Wednesday, Thursday, Friday ... etc;

Cheers
RemyZ

 

[SkipVought]

Hi,

You can right-Click the sheet navigation controls in the lower left, and you have a worksheet popup list.

Otherwise you must create the list...

dim ws as worksheet, lRow as long
lRow = 1
for each ws in worksheets
  cells(lRow, "A").value = ws.name
  lRow = lRow _+ 1
next

Use Data > Validation - LIST to assign this range.
Or use Forms DropDown and assign this range to the Input Range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DAmoss]

Hi SkipVought,

I didn't actually mean a 'Worksheet' list itself, I meant a dropdown list on a worksheet, hence an example using the following would be great:

Monday, Tuesday, Wednesday, Thursday, Friday ... etc

Is it possible to declare the above items in an array and then pass them to a dropdown list routine, which is then attached to a range of cells?

Can someone show me how to do this in Excel?

Cheers

 

[xlbo]

File size will be greater as you are creating a new object

In any case, you can do this in 2 ways - you can use the combobox from either the Forms toolbar or the Controls Toolbox toolbar - each of them has a property where you set the source for the dropdown box which can either be a range of cells or a comma seperated list

Simply make the toolbar visible, click on the combobox and drag it onto the sheet then right click to see the properties and go from there...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I'd suggest making a LIST of the names you want in the order that you want on another TAB. NAME THE LIST, let's say DayList

Select the CELL that you want the DropDown in.

Data > Validation - LIST and enter =DayList in the textbox.

You will have an in-cell DropDown in each of those cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}