Excel selecting multiple values in a drop down list

QuestionEdit

I am using2007. In col E I have a drop down list and I want to be able to select multiple items from it. I used the following code from you It used to work before but now it does not. When I select one value and then another, it keeps only the new value. I also would like this to work if a column is added. So I would like to use a named col and use it in code. Thanks K On Error Resume Next

On Error GoTo exitHandler

' rngDV = Intersect(Range("Q:Q,R:R), rngDV) Set rngDV = Range("E:E")

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 oldVal = "" Then
   'do nothing
 Else
   If newVal = "" Then
   'do nothing
   Else
   Target.Value = oldVal _
     & ", " & newVal
   End If
 End If

End If

exitHandler: Application.EnableEvents = True End Sub

AnswerEdit

Kaly,

see additional comment at the end:

If it used to work and now it doesn't and you didn't change anything, then I suspect that VBA code has been disabled because of your security settings. The code is what allows you to make multiple selelections. It the code doesn't run, then you are back to the default behavior of a datavalidation dropdown box.

that said, the code you have pasted in here is only partial. It should start with a declaration of the change event - so there is a lot of code you haven't pasted in.

But it does look like you have changed something in the code

' rngDV = Intersect(Range("Q:Q,R:R), rngDV) Set rngDV = Range("E:E")

as I recall, rngDV is initially set to all the data validation cells in the worksheet. It was then intersected with columns Q and R so the code only ran against a cell with data validation in columns Q and R. It appears the code has been changed to run against any cell in column E whether it has data validation or not.

that said, I added the event declaration and put this code in a sheet module:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next

On Error GoTo exitHandler

' rngDV = Intersect(Range("Q:Q,R:R), rngDV) Set rngDV = Range("E:E")

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 oldVal = "" Then
  'do nothing
Else
  If newVal = "" Then
  'do nothing
  Else
  Target.Value = oldVal _
    & ", " & newVal
  End If
End If

End If

exitHandler: Application.EnableEvents = True End Sub

it ran fine for me although it worked both for cells with data validation dropdowns and for cells that did not contain data validation dropdowns as long as they were in column E. I you want it to work with a named range then if that name is Mycolumn

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next

On Error GoTo exitHandler

' rngDV = Intersect(Range("Q:Q,R:R), rngDV) Set rngDV = Range("Mycolumn") '<== changed line here

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 oldVal = "" Then
  'do nothing
Else
  If newVal = "" Then
  'do nothing
  Else
  Target.Value = oldVal _
    & ", " & newVal
  End If
End If

End If

exitHandler: Application.EnableEvents = True End Sub

Kaly, If you meant xlsx file, then the macro would be stripped out - not just disabled.

If you meant what you said - it was saved as an xls file, then there should be no problem running the macro. I run macros in xls files in Excel 2007 (and in 2010) and earlier versions on a daily basis. The only reason they would not run (given you haven't disabled events) is if security settings disabled macros or you were queried to enable macros and responded no. I am glad you got it going, but I stand by my original statement

> then I suspect that VBA code has been disabled because of your security settings.

(if you disabled events either due to errors or for some other reason, closingand then reopening it would re-enable events if vba was enabled)

If you need further help, please seek it from whomever you think is the "expert of the Month" as my answers apparently don't measure up. Have a pleasant day.