Select Multiple Names from Cell

Hi folks,

i would like to know , how in excel i can select multiple persons , whereras i know how to select single person from dropdown list ..any ideas

Regards,
SID
infiniti7181Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The Microsoft Office blog has this article that I believe explains how to do it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
That article doesn't seem to be available so try this. Add this code that I found on the web to the sheet with the validation list. To use it you select the values one at a time and it creates a comma separated list.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then
        Exit Sub
    End If
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        Exit Sub
    End If
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                Target.Value = xValue1 & ", " & xValue2
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Martin

I have tweaked the code for not to accept the duplicate selection from the drop down list i.e. if a selection already exists in the cell, the code will not allow to select the same value from the drop down again.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then
        Exit Sub
    End If
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        Exit Sub
    End If
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If InStr(xValue1, xValue2) = 0 Then
                    Target.Value = xValue1 & ", " & xValue2
                Else
                    Target.Value = xValue1
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Open in new window

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

infiniti7181Author Commented:
Hi ,

Thanks for your quick response . It works fine and i understood the logic . thanks for your support.

Regards,

Aaron
infiniti7181Author Commented:
HI
Could you please advice how can i have multiple entry instead of comma to be newline in the same cell .

Regards,

Aaron
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The following line will do what you are asking......

Target.Value = xValue1 & vbCrLf & xValue2

Open in new window


Also were you interested in selecting duplicate values from the drop down as you didn't respond to my post?
infiniti7181Author Commented:
Hi . Thanks for the reply . where do i add it ?
infiniti7181Author Commented:
The objective is to select the multiple values from the list . These values will not be duplicated .

Eg: If i select red, blue , green - multiple list for me would be red,blue,green  or red,blue

Not red, blue , blue etc . Also i would like to have in different newline (alt+enter) , just to make it as good formatted document .

Regards,
SID
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The objective is to select the multiple values from the list . These values will not be duplicated .
If this was the case, why didn you accept the Martin's solution as the only accepted solution?
Did you not read my post before accepting that solution?

If this is the case, you may use Request Attention to reopen the question and may distribute the points by accepting both the solutions.

This code is for Worksheet Change Event and must be placed on the Sheet Module not on Standard Module. To apply the code to your workbook, follow these steps....

1) Open your workbook.

2) Right Click the Sheet Tab on which you want this code to work --> Select View Code

3) Now paste the code given below into the opened code window.

4) Close the VBA Editor.

5) Save your workbook as Macro-Enabled Workbook.

6) Now your workbook will be saved with the same name but with .xlsm file extension.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then
        Exit Sub
    End If
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        Exit Sub
    End If
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If InStr(xValue1, xValue2) = 0 Then
                    Target.Value = xValue1 & vbCrLf & xValue2
                Else
                    Target.Value = xValue1
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Open in new window


Hope this helps.
infiniti7181Author Commented:
Hi ,
Thanks all , it is working as per my expectation . I have emailed moderator for equal point distributor.
Thanks for your support again .

regards,
Aaron aka SID
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Aaron!
Glad I could help.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.