# Excel multi selection list results into 1 cell



## DJ_Jingles (Oct 9, 2006)

Hi,

Have been fighting with a spreadsheet for a few days now, and after trying out various "solutions" found on the Web all to no avail I thought I would go the guys ( and girls) who know!

I have a spreadsheet ( attatched ) and the purpose of it is for me to be able to select reasons why hire vehciles do not pass a daily check. There are, sadly, sometimes occassions where there are more than one reason so I would like to be able to selct 1,2,3 or 4 reasons from my drop down box and have the results shown in the "same cell" seperated by a comma. I can then use that data in some charts on aanother sheet.

Does anybody know how I can do this.

I know I need some VBA code but when I have tried copying/pasting from examples on the web it doesnt work.

Please please please can someone stop me going mad!>!>?!?!

Thanks

Rob ( DJ_Jingles)


----------



## tbocat (Oct 10, 2006)

Hi Rob ( DJ_Jingles),

I used to need solve Excel problems with help from online resources.

Remembering how good people were to me in those days, I thought I would see if I could write some code to assist you.

If I understand your challenge, perhaps the attached will help.

Please reply if this helps or if I didn't get it.

HD (tbocat)


----------



## Jimmy the Hand (Jul 28, 2006)

Hi Rob,

I think it's not possible to do multiple selection in a validated cell. Nevertheless, I've created a workaround for you, and I think it's sastisfying. The basics of operation is:

There's a macro for OnChange event of Sheet1.
This macro runs every time when any cell's value changes. It runs when you select something from the drop-down boxes. (I wasn't able to find any other events that occur when cell values are changed.) 
The macro then examines the changing cell (target). If it doesn't have a validation rule set then runtime error occures. (The error handling routine does nothing, except preventing the run from breaking because of an error.)
If the changing cell has a validation rule, then the cells value is added to its *previous* value, and then the cell gets this total string. To know the previous value, I needed a secondary sheet (Sheet2), where I store all previous values.

Generally, that's it. A few more things:
1. This way you always concatenate strings, so deleting values is not possible. As a workaround I put in an exception. IF the selected string from the drop-down list is a dot (".") then the new value will be empty string. This is essentially a delete. 
2. There is that public boolean variable called InWork. It's extremely important, because without it the macro initiates it's own run by writing into the cell, so it gets into an endless loop, possibly stack overflow error.
3. I think it's recommended to switch off warning messages if the entered data doesn't meet the validation rules.

So the code is here, and also see the attachment.

```
Public InWork As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)
    If InWork Then Exit Sub
    InWork = True
    On Error GoTo NonValidatedCell
    If Selection.Validation.Type = xlValidateList Then
        ColAbs = Target.Column
        RowAbs = Target.Row
        If Sheets("Sheet1").Cells(RowAbs, ColAbs).Value = "." Then
            TotalString = ""
        Else
            TotalString = Sheets("Sheet2").Cells(RowAbs, ColAbs).Value & ", " & Sheets("Sheet1").Cells(RowAbs, ColAbs).Value
        End If
        If Left(TotalString, 1) = "," Then TotalString = Mid(TotalString, 3)
        Sheets("Sheet1").Cells(RowAbs, ColAbs).Value = TotalString
        Sheets("Sheet2").Cells(RowAbs, ColAbs).Value = TotalString
    End If
    InWork = False
    
    Exit Sub
NonValidatedCell:
    InWork = False
End Sub
```
The code must be put onto the class module of Sheet1

Edit: Removed attachment, see my next post


----------



## DJ_Jingles (Oct 9, 2006)

TBOCAT - Thanks so much for taking the time to assist me. Your programming does achieve a lot more than I had been able to do and goes some way to solving my issue!


JIMMY THE HAND - Had just finished reading TBOCTAS post when you replied too! All I can say is "YAHOOOOOOOOOOOOOOOO" !!!!


----------



## DJ_Jingles (Oct 9, 2006)

TBOCAT - Thanks so much for taking the time to assist me. Your programming does achieve a lot more than I had been able to do and goes some way to solving my issue!


JIMMY THE HAND - Had just finished reading TBOCATS post when you replied too! All I can say is "YAHOOOOOOOOOOOOOOOO" !!!!
The work you have done is "EXACTLY" what I needed and works a treat. You are a Star and I cant believe the effort you guys go to to help us dweebs out!!

I cant wait to get to work( For a change ) to transfer this code across to the live sheet!! Thanks again - truly.

Rob


----------



## Jimmy the Hand (Jul 28, 2006)

I've improved the macro a bit. See attachment. I don't need Sheet2 anymore, I use Application.Undo, instead, to get the previous cell value. It's better this way, because it eliminates some problems. Unfortunately these problems would be too time comsuming to describe here, so I don't make the effort. But the previous macro version is still there, REM-ed out, so you can experiment with it if you want.

by the way... what's a _dweeb_?


----------



## MMog (Feb 26, 2008)

Dear Jimmy the Hand, 

I had a problem smiliar to Rob and your macro is just MAGIC! However, I have 2 problems:

I need to run this macro for only some validated cells. You see, I am using a sheet with a lot of validated cells but in most of them I want only one reply, whilst in others I need a string. How can I specify a range for the macro you sent? 

Also, I am running another macro that enters real time (created date I call it) in a cell everytime another cell is filled in. E.g. When project name is entered in Column A, Column B automatically inserts the time and date. How can I keep this macro along with the previous one on the same sheet? 

DESPERATE for you HELP! THANK YOU

MMog


----------



## slurpee55 (Oct 20, 2004)

MMog, first welcome to the forum!
However, it would have been best if you had started your own thread - perhaps with a link to this one - rather than piggy-back onto someone else's. (If you notice, this thread is from 2006).
Don't start another one now - that would be double-posting, another thing not to do. (I would suggest you read the rules at http://www.techguy.org/rules.html carefully.
I am no coder, unfortunately, but I will try and get ahold of some of the gang here and send them your way.


----------

