Selectable list box

Hello all,

Can a list box (or other type) be created that would come up and allow a selection of multiple items. I do not need the items to be viewable beyond the selection, only to be ablle to sum different segments of the rows that were selected.

As example: Upon clicking in cell B 141 of the sheet a box would come up that would include the descriptions (in column C) of all items that are between rows 1 and 140 that have identifeid by a "y" in Column B. Then you could select as many as you desired and the sum of the quantity (column d) of the selected items would then appear in D141.

Would something like this be possible?

Thanks,
Mark

Set the .MultiSelect property of the list box to True.

Thanks for the reply, however I know nothing about the creation of a list box. Could you help with a little additional info?

Thanks,
Mark

Yes, but after I'm off work. (Your problem is more complicated than I thought. Poor time mgt on my part.)

Great!!! I look forward to it. This is a problem I have wanted solved for a long time. I have tried every way and failed.

Thanks,
Mark

I haven't abandoned the problem. I'm having trouble reading a multi-select forms list box. I think the way to go is:

When B141 is selected a userform is created. It has the list box you want and a cancel button and an OK button.

Select the items you want and the running total will be put into D141.

Press Cancel, everything disapears and the old value is restored.
Press OK, userform goes away, leaving the new sum in D141.

Selecting a different cell when the userform is present is the same as pressing OK.

What version do you have, (i.e. is it '97 with only modeless uForms?)

2003. I think you are speaking in tongues!!! LOL I am an extreme novice with anything beyond basic excel functions. Your help is appreciated.

Thanks,
Mark

I think this will do what you want. I wrote it assuming that you were working on Sheet1, you will have to adjust where needed.
You said that you were new to this so:
1) Back-up your workbook somewhere safe, in case the idiot from the internet (that would be me) is wrong.
2) Open a new Excel session with your test version of the workbook.
3) Use Tools - Macro - Visual Basic Editor to open the VB editor.
4) Use the Insert menu to insert a Module (not a Class Module).
5) Copy the first batch of code below and paste it into the module window that opens.
4) Use View to open the Project Explorer. Open folders until you see Sheet1. Double click on the icon and a code module should open. Paste the second piece of code into that window.
5) Save the workbook from the File menu.
6) From the File menu, "Close and Return to Microsoft Excel".

Place this code in a new normal code module:

Please Login or Register  to view this content.

In the code module for Sheet1:

Please Login or Register  to view this content.

Select cell B141 and a ListBox will appear.
The list will be those entries in C1:C140 that have a matching "y" in column B. Select any number of entries on that list and the sum (taken from column D) of the selected entries will be put in D141. Either pressing Done or using the mouse to select some other cell will dismiss the list, leaving the sum in D141. Pressing Cancel will dismiss the list and return D141 to the value when the list first appeared.

The list appears with nothing selected. With up to 140 entries, that could be a bother. I would like to have the list appear with the same items selected as the last time, but the probablity that one list will have different members than the last has me stymied for the moment.

Thanks so much for the help. I followed the instructions and when I went back to Excel the following error came up "compile error" "syntax error". I then hit Ok and the visual basic editor came up and has the line that says Sub OKdone () (that is near the bottom of the lines I copied) highlighted in yellow. Any ideas?

Mark

No, I have no idea. Perhaps there is a chr(160) hiding somewhere in the routine.

What is a chr 160???

Thanks,
Mark

Chr(160) is a non-breaking space. It is common on the internet, but as part of a code module, it causes problems. This site is good about keeping it away, but it is a suspect.

I just started a new workbook and downloaded the routine from this site. No problems. Did you get all of the last line, "End Sub"? (the only space that is important is the one between the words.

Thank you so much!!! My copy/paste was off a little. Works great!!!

I do have another question though -- I was hoping I would be able to modify this to get the end result. I don't know exactly how, and I am not asking to infringe upon any more of your time. Will the code you provided allow the cell B 141 to extend to cell B142 and so on down for maybe 20-25 rows with minor adjustments? Also if rows are inserted above can this be shifted down (or up) as the case may be?? Thank you again as you have provided me with a terrific start!!!

Thanks,
Mark

Yes, it can be done. The solution to keeping track of where the trigger cell is will also help give the machinery for the recall last selection problem that I mentioned above.

One question, the decriptions in column C, are they unique?

Yes, column C is unique.

One more, is the first row headers or data?

Data begins in row 4. The data may be as short as row 50 and as long as row 600 or more.

mark

Sounds good.

Here are the modifications that I've made.

Put this in the normal module:

Please Login or Register  to view this content.

And this in the sheet's code module:

Please Login or Register  to view this content.

Exactly where the routine writes data is based off of the last entry in column C.
Consider the row immediatly below that cell.
If the user selects the cell at column B of that row, the list box will appear.
The user's selections from the list box will decide the sum displayed in column D of that same bottom row.

The routine remembers from list box to list box what was selected the last time and appears with the same items selected. By "same item", I mean the same number in the list box. It will remember that "the top item was selected" and select the top item in a new list box, even though (by changing y's in col B) the top item is different than the last list's top item.

Also, I am concerned about the list box being activated by the selection of a moving cell, that doesn't look different than any other cell. So I arranged for the list to be activated by seleting cell B1, which doesn't move.

Thanks!!! I don't have time to try it now, but will tonight or in the morning. I will update then.

Thanks,
Mark

Well....

It was trying to work, but my sheets were named differently so the debugger kept coming up. I tried to alter it to work and now nothing I will continue to mess with it and let you know.

Thanks,
Mark

The sub startValues is where the sheet name is controled. I wrote the routines so that only that line needs to be adapted to your sheet name.

It is working now -- Thank you very much.

mark medlin