Multiple Selection from Drop Down List

[paankadu]

I have a list created on a separate sheet in my workbook named List. On 2 other worksheets (Day Log, Night Log) I want to be able to select multiple items from that list. The list is employee names and it is labeled Operators. My drop down list is on Day Log cell B2 and I would like to be able to select 1 or more names and have them in the same cell, separated by a comma. Any help would be appreciated.

Thanks so much!
Angie

 

[steveh1873]

Hi JBeaucaire,

I have made a drop down list named "Region" (In cells A3:A2500) and a dependent drop down named "Area" (In cells B3:2500), Both are made using named ranges 'Region' and 'CGM', 'HIOW', 'HLNY', 'Merseyside' or 'WY' (depending on 'Region's selection)

I have copied and pasted the following: (I have amended the target range however)

Private Sub Worksheet_Change(ByVal Target As Range)
'JBeaucaire 10/2/2009)
'These two macros create a multi-choice dropbox
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target <> "" Then
If Prior <> "" Then Target = Target.Text & ", " & Prior
Prior = Target
Else
Prior = ""
End If
End If
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then _
Prior = Target.Text
End Sub


It works...to a point... As I'm working through the sheet, I get the following prompt "Run-time error '94': Invalid use of Null" and am directed to the line 'Prior = Target.Text' (highlighted in red.

Can anyone help solve this or provide a new set of code based on the info i've provided?

All help is appreciated...

 

[jbeaucaire]

See here.

The sample codes on that page may make it clearer, it appears you may be missing the PUBLIC declaration at the top of the page when utilizing macro version #1. You might also look at the second version on that page which suppresses duplicates.

 

[steveh1873]

See here.

The sample codes on that page may make it clearer, it appears you may be missing the PUBLIC declaration at the top of the page when utilizing macro version #1. You might also look at the second version on that page which suppresses duplicates.

Apologies for the delay in response, but yeah, fantastic. Managed to utilise the non-duplication code displayed in the link too.

Thank you