multiple pick from drop-down list

[johnrb7865]

Does anyone know how to setup a drop-down type (or other type) list where I
could pick multiple selections from the list and have it populate each
selection in a different cell?

Thanks,
John

 

[Rick Rothstein \(MVP - VB\)]

Drop-downs do not lend themselves to multiple selections (what should be
shown in the text area when the drop-down is not dropped down? You would
probably be best off using a ListBox (it supports multiple selections). Can
you give a more detailed description of what you want to do?

Rick

 

[johnrb7865]

Hi Rick, we are building a spreadsheet to handle pay items and quantities for
a department of transportation. For example, the spreadsheet has a 10 digit
pay item code, then a description, then a unit (like linear feet of guard
rail or tons of asphalt). We have setup drop-down lists through data
validation that allow the user to pick a single pay item and it will
automatically populate the description and unit and setup the columns to
calculate the quantity based on the unit. Typically we have a drop-down in
the top row and once selected everything is calculated based on that
selection. Now a user wants to be able to select multiple items and have it
place each one in a cell across the top. Then our formula's will take off
from there. So, obviously the drop-down list we are using to set each pay
item individually won't work. Any other thoughts on how to do this would be
great.

Thanks,
John

 

[Rick Rothstein \(MVP - VB\)]

I'm retired now, but I worked 32+ years for in the New Jersey Department of
Transportation as an engineer in Road Design. Out of curiosity, which DOT
are you with (wouldn't it be cool if you said NJDOT)?

Alright, I'm not 100% sure how I would invoke its appearance yet, but I'm
leaning to suggesting a modeless UserForm (so it can remain visible and
still allow the user to interact with the worksheet) with a ListBox and
CommandButton on it. The only concern I have with this overall multi-item
selection model is the accidental over-writing of existing data. What if the
user wants to replace 3 items, but accidentally selects 4 items from the
list? Obviously, this is all doable in code, but I think the effort to
control everything might get kind of large... do you warn the user about all
over-writes (even if there are 100 of them)?... do you maintain some kind of
undo buffer?... and so on. These issues don't come up with the single-cell
drop-down model... the user sees what he/she is changing.

Rick

 

[johnrb7865]

Hi Rick, wow, small world. We are actually a consultant working on the CADD
systems for the Colorado DOT. These quantity calculation spreadsheets all
feed a printable sheet that gets linked to MicroStation so the user can place
it in the standard border and plot with their plan set. If you like I can
send you a copy of one we have done so you could see what we are trying to
get to. As to your suggestions, I don't think the user will have to worry
about overwriting data as these are broken up by discipline. For example, we
have one for drainage with inlets and culverts, etc. and a different one for
bridge and even within disciplines we break them down to surfacing and
guardrail, etc. Each sheet will have anywhere from a couple pay items up to
30. Probably not much more than that. Does that help?

Thanks,
John

 

[Debra Dalgleish]

There's a sample file here that uses programming to allow multiple
selections from a data validation drop down:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0017 - Select Multiple Items from
Dropdown List'

 

[Rick Rothstein \(MVP - VB\)]

Yep, MicroStation is what we were (they still are) using when I retired in
2002. As I recall, Version 8 had just come out (we hadn't begun adopting it
as of then); I'm pretty sure we were using Version 7.something. Of course, I
go back to when NJDOT first first got into electronic design with an
Intergraph CADD system (a predecessor to MicroStation before Intergraph
acquired an interest in Bentley and prior to Bentley splitting back away
from Intergraph) running on a DEC mini-computer. I wrote a large,
comprehensive FORTRAN IV program (similar to what you are working on) back
in 1989-1990; it was my first and only FORTRAN program (I learned FORTRAN
while developing this project). I called it EDQ (for Estimate and
Distribution of Quantity, the name of the physical contract set sheets it
replaced) and it was a pretty "spiffy" program for its day. This program had
a component that automatically generated the entire Estimate and
Distribution of Quantity sheet for incorporation into the contract set when
it was compiled.

Okay, enough history.<g> The reason I had any concern is because in New
Jersey, we had over 3500 Standard Items that could be included in a
project... the Drainage section of the item list made up probably 30-40% of
that list (there was an individual Standard Item for every size of
everything!)... it is hard to imagine an Item list that is as small as the
one Colorado seems to work with. Anyway, sure, send a copy of what you have
and I look at it to see if I get any ideas that might help you out.

Rick

 

[johnrb7865]

Hi Rick, we now have figured out code to create a list box and select
multiple items. We do not know how to get the selected items to populate
rows, one below the next, along the left side in Excel. Any thoughts?

 

[Rick Rothstein \(MVP - VB\)]

Assuming an ActiveX ListBox, iterate the List property, something like this
(untested, off-the-top-of-my-head)...

For X = 0 To ListBox1.ListCount - 1
Cells(X,1).Value = ListBox1.List(X)
Next

Rick

 

[johnrb7865]

Hi Rick, we got an "application-defined or object-defined" error on the
second line. Any thoughts?

 

[Rick Rothstein \(MVP - VB\)]

Where did you get or call the ListBox from? What is its (exact) name?

Rick

 

[johnrb7865]

Rick, you are a genius. We got it to work! Thanks for all your help, have fun
in retirement. Let all your friends at NJDOT know that you are still working
with DOT's

 

[Rick Rothstein \(MVP - VB\)]

Rick, you are a genius. We got it to work!

Great! I guess you figured out the Cells(X,1).Value should have been
Cells(X+1,1).Value as there is no Row 0.

Thanks for all your help

You are most welcome.

have fun in retirement.

I have been and, God willing, plan to keep on doing so.

Let all your friends at NJDOT know that you are still
working with DOT's

Will do.<g>

Rick