Drop-Down Macro

[Michael Link]

I found the following macro during some internet scrounging. If you have a
drop-down data-validation list in Column C, it builds an accumulative list in
a cell from items selected from the list. (IE, if my validation list is of
Jeeves, Wooster, and Tuppy, each time I select Jeeves from the drop down, it
will add it to the cell with a comma: "Jeeves, Jeeves, Jeeves, Jeeves,
Jeevesr":

Option Explicit

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

Unfortunately, I really know almost (in fact, maybe exactly) nothing about
VBA. I DO know that I have a workbook of 10-odd sheets, each one of which has
three to four columns which have drop-down validations which I need to apply
this macro to. I know how to change the column the macro refers to (changing
"3" in the line "If Target.Column = 3 if" to the correct column number), but
I don't know how to make it so thiat macro works for multiple columns in each
sheet (say, Columns 4, 8, 10, and 18) all the time.

What is the best way to do this? I know this part of the newsgroup is for
users who know what they're doing, but any help you can provide to a total
neophyte would be much appreciated!

Desperate in Columbus

 

[Bernie Deitrick]

Michael,

Try the version below.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String
Dim newVal As String
Dim myType As Variant

If Target.Count > 1 Then Exit Sub

'Make it only work on columns 4, 8, 10, and 18
If Target.Column <> 4 And _
Target.Column <> 8 And _
Target.Column <> 10 And _
Target.Column <> 18 Then Exit Sub

On Error GoTo exitHandler
myType = Target.Validation.Type

Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

 

[Michael Link]

Thanks for your suggestion! It looks like that will work! interestingly, I
received other suggestions from some other folks not on the newsgroup, and
they all seem to work. Definitely a "more than one way to skin a cat" sort of
moment.