Solved: Multiple selections

[eagle_eyes]

Hopefully someone can help me end this torture...

I have a worksheet with a list of names in column F that are currently in a drop-down list. All I want to be able to do is change it somehow so that a user can select more than one name at a time and those names to show up in another column.

I have no VBA experience and based on the last three weeks of spinning my wheels, no VBA knowledge. I have found a few macros that are supposed to accomplish this seemingly simple feat but I haven't been able to modify them to work with my spreadsheet.

If there's anyone out there that can tell me how to do this and, if it requires using the VBA editor, exactly how to install it on my worksheet, I would be eternally grateful.

Thank you.

[lucas]

By drop-down list do you mean a data validation list?

[eagle_eyes]

Yes, that's what it's in right now. I tried just selecting the "multi-select" option but I was still only able to select one item at a time.

[compariniaa]

i think i can help you, but i don't know a ton about vba, so this is more of a roundabout way of doing things.

first, you're going to have to get rid of your drop down list (i'm not 100%, but I'm pretty sure you can only select multiple items from something if it's a listbox). That being said, go to View>Toolbars>Control Toolbox. after getting the control toolbox up, find the listbox button and make your listbox. Right-click the listbox and choose "properties" Under properties, edit the name of the listbox (this isn't necessary, but useful for keeping track of things if you're trying to read your code). then on the properties window, find "ListFillRange" and put in the range where your data is found. Next, right-click the listbox and click "view code" when that window pops up, put this in there:
[vba]Private Sub Field_Box_Change()
'field_box was the name of my listbox, so depending on the name you choose, change every "field_box"
'to the name you chose for your listbox

Dim iCtr As Long
Dim DestCell As Range
Dim HowMany As Long

HowMany = Me.Field_Box.ListCount

Set DestCell = Me.Range("K3")

'instead of K3, put the range where you want the choices sent
'in this format: sheets("your sheet name here").range("cell where it's going")
'unfortunately, this is going to spit it out as a number, with 0
'being the index number for the first item on your list
'to get around this, I used an If...VLOOKUP combination in my
'true destination cell. If I wanted A1 and down to show their choices,
'I put a formula like this from A1 and down:
'IF(K3="","",VLOOKUP(K4, 'Cell References'!$H$2:$I$41,2,False))
'the cell references sheet is a sheet that remained hidden
'but it contained any references i needed
'i also made K3's (where the index number was shown) text
'color match the background color so it didn't show up

DestCell.Resize(HowMany).ClearContents

With Me.Field_Box
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = iCtr
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub[/vba]

Like I said, it's a roundabout, sloppy way to do it, but I don't know any better. good luck!

[ALe]

Look at this. Hope it can be of help.

[lucas]

I don't think it can be done with data validation drop down but as you can see from the replies a listbox will do this for you. If you wish to use a form check out Ken Puls kb entry:
http://vbaexpress.com/kb/getarticle.php?kb_id=369

[eagle_eyes]

Thanks for all of your replies. Ideally what I want it to do is put the selections into one cell, separated by a comma. I have no idea which message board I found this spreadsheet on, but the sheet "SameCell" does exactly what I want my sheet to do. I just can't figure out how to modify it so that it has my list of items in it.

[lucas]

but you said you wanted to select more than one at a time...?

[eagle_eyes]

Yes, I want to select more than one at a time and have all of the results show up in one cell, separated by commas.

[lucas]

Open the workbook and click on Tools-Protection-Protect sheet
Then click on one of the cells and click on Data-Validation
The code works on the validation in the cell
to see the code -, right click on the sheet tab and select "view code"
If you still have questions post back

[lucas]

that sheet does not allow you to select more than one at a time....

[lucas]

tip: after you duplicate the list style to column F validation you will have to change this line in the code from column 3 to 6
[vba]
If Target.Column = 3 Then
to
If Target.Column = 6 Then
[/vba]

see cell F2 in the attachment

[eagle_eyes]

I see what you mean about not being able to select more than one. It doesn't really matter, I guess, about whether the selections are made all at the same time or one after the other as long as all of the selections end up in one cell, separated by commas.

I opened your worksheet and wasn't able to get the Tools menu to drop-down to be able to unprotect it. With the protection on, I couldn't make any changes at all to F2.

And it looks like you had the same problem I did which is, once you move the Samecell worksheet from that workbook, it no longer places more than one entry in a cell. I tried changing the Target.Column but it still didn't work.

The madness continues...

[lucas]

Hi Eagle Eyes,
This is not madness....its just simple data validation and sheet protection.
I have no idea why you can't go to tools-protection and unprotect the sheet....I can and you should be able to also.

note that this workbook has workbook open code which protects the sheet every time its opened...

I did in my haste forget in the previous attachment to unprotect the sheet and then right click on cell F2, select format cells and select the protection tab and unlock the cell.....that explains why it didn't work the first time but it works now for me even after closing and reopening the workbook.

If you have any more quesions be sure to post them and if you wish to upload your workbook I can help you set this up but it would be better for you to understand what is going on.

unprotect the sheet
set up the data validation in the column you wish to use according to the example given.
Unlock the cells involved in the data validation while sheet is unprotected
change the column number in the code for the worksheet....
thats really it because when you close the workbook and reopen it the sheet protection will be in place again.

new attachment

[eagle_eyes]

IT WORKS!!!

Thank you so much for your patience and direction! Are there any books you could recommend for me to learn from and use as a reference?

[lucas]

My pleasure. Glad you got it going your way. Try this link for a lead on good book references:
http://vbaexpress.com/forum/forumdisplay.php?f=75

ps don't forget to mark your thread solved....