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


New Member
May 21, 2016
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

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

If newVal = "" Then

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

End If
End If
End If

Application.EnableEvents = True
End Sub

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

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Latest member