D
DoveArrow
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.
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
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.
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