VBA code for selecting multiple items in a single cell pulled from a data validation list

[julesmck]

Hi, I'm new to Excel and I'm stumped. I've searched this site and elsewhere trying to find an answer to my question but I haven't had any luck finding one I can understand.

Here's the problem:


I'm creating an inventory of all the documentation we have about a product (in case it helps, I've uploaded the workbook I'm trying to get to work here). I'm trying to capture what each document is about by associating if with one or more subjects - for example, a document about uploading documents might contain information about 'metadata,' mandatory 'property fields' and the act of 'uploading' itself. I'd like to be able to associate a single document with all three of those subjects by selecting each of them in a single cell (the values are pulled from a data validation list in another spreadsheet called 'Subject totals.')

Can anyone tell me how to:

• Set up the workbook so that I can select multiple values using checkboxes from a single cell
• Set up COUNTIF so that it retrieves an accurate count of all the documents associated with a particular subject (in the example above, the document about uploading documents would count for three subjects: 'metadata,' 'property fields' and 'uploading' subjects).

To solve the first problem, I tried pasting the following code into the workbook, but nothing happened:


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


Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then

Else
If newVal = "" Then

Else
Target.Value = oldVal _
& ", " & newVal


End If
End If
End If


exitHandler:
Application.EnableEvents = True

End Sub​

Any guidance would be very much appreciated. Thank you!!