I'm very new to working with Visual Basic in Excel, so I'm hoping
someone can help me out.

I found this neat little video tutorial on YouTube explaining how to
create a data validation field which allows users to store multiple
items in a single field, separated by a comma.

http://www.youtube.com/watch?v=8x6YU...eature=related

Using this code, I've created a spreadsheet called "Course Sections."
On this spreadsheet is a column called "Faculty Name." Each of the
fields in this column are tied to a data validation table stored on a
separate spreadsheet, called "Faculty."

Now here's where I'm running up against a wall. In addition to storing
the faculty person's name, the "Faculty" spreadsheet also lists a
faculty ID number for each person. I'd like to take that faculty ID
number and add it to a second column on the "Course Sections"
spreadsheet, so that it would look something like this:

Faculty Spreadsheet:
Faculty Name; Faculty ID
Mickey Mouse; 10111
Donald Duck; 10112
Uncle Scrooge; 10113
Launchpad McQuack; 10114

Course Sections Spreadsheet:
Section; Faculty; Faculty ID
ENG-101; Mickey Mouse; 10111
ENG-102; Donald Duck, Uncle Scrooge; 10112, 10113
ENG-103; Donald Duck, Launchpad McQuack, Mickey Mouse; 10112, 10114,
10111

Using the code from the tutorial as a guide (see below), I'm assuming
I would need to do something like the following:

1) Perform a lookup to find the Faculty ID on the "Faculty"
spreadsheet, based off the newVal string.
2) Store that Faculty ID in a string. (Let's call it newID.)
3) Append that ID number to the data currently stored in the "Course
Sections" spreadsheet.

Now steps 2 and 3 I don't think would be too hard. In fact, I imagine
they would look something like this.

Dim newID as String
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value & ", " & newID

The problem is, I don't even know how to begin coding step 1. Anyone
have any bright ideas?

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

On Dec 21, 6:27*pm, DoveArrow wrote:
I'm very new to working with Visual Basic in Excel, so I'm hoping
someone can help me out.

I found this neat little video tutorial on YouTube explaining how to
create a data validation field which allows users to store multiple
items in a single field, separated by a comma.

http://www.youtube.com/watch?v=8x6YU...eature=related

Using this code, I've created a spreadsheet called "Course Sections."
On this spreadsheet is a column called "Faculty Name." Each of the
fields in this column are tied to a data validation table stored on a
separate spreadsheet, called "Faculty."

Now here's where I'm running up against a wall. In addition to storing
the faculty person's name, the "Faculty" spreadsheet also lists a
faculty ID number for each person. I'd like to take that faculty ID
number and add it to a second column on the "Course Sections"
spreadsheet, so that it would look something like this:

Faculty Spreadsheet:
Faculty Name; Faculty ID
Mickey Mouse; 10111
Donald Duck; 10112
Uncle Scrooge; 10113
Launchpad McQuack; 10114

Course Sections Spreadsheet:
Section; Faculty; Faculty ID
ENG-101; Mickey Mouse; 10111
ENG-102; Donald Duck, Uncle Scrooge; 10112, 10113
ENG-103; Donald Duck, Launchpad McQuack, Mickey Mouse; 10112, 10114,
10111

Using the code from the tutorial as a guide (see below), I'm assuming
I would need to do something like the following:

1) Perform a lookup to find the Faculty ID on the "Faculty"
spreadsheet, based off the newVal string.
2) Store that Faculty ID in a string. (Let's call it newID.)
3) Append that ID number to the data currently stored in the "Course
Sections" spreadsheet.

Now steps 2 and 3 *I don't think would be too hard. In fact, I imagine
they would look something like this.

Dim newID as String
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value & ", " & newID

The problem is, I don't even know how to begin coding step 1. Anyone
have any bright ideas?

Option Explicit
' Developed by Contextures Inc.
'www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
* *'do nothing
Else
* Application.EnableEvents = False
* newVal = Target.Value
* Application.Undo
* oldVal = Target.Value
* Target.Value = newVal
* If Target.Column = 3 Then
* * If oldVal = "" Then
* * * 'do nothing
* * * Else
* * * If newVal = "" Then
* * * 'do nothing
* * * Else
* * * Target.Value = oldVal _
* * * * & ", " & newVal
* * * End If
* * End If
* End If
End If

exitHandler:
* Application.EnableEvents = True
End Sub

Send me this msg and your file and I'll take a look.
dguillett1 @gmail.com