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
' add your criteria for columns here
If Target.Column = 3 or Target.Column = 5 or _
Target.column = 7 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
--
Regards,
Tom Ogilvy
"LTaylor" wrote:
> I'm sorry I'm new at this. This is for a list dropdown. I see that this
> code is referencing target.column = 3
>
> When I use this code it does allow more than one choice in column 3 but, how
> do I make it reference more than one column? Thank you
>
> 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
>
>
>
> "Tom Ogilvy" wrote:
>
> > I doubt anyone knows what you are talking about. Post the code that you
> > claim allows you to do this. Also say what kind of dropdown you are using.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "LTaylor" wrote:
> >
> > > I have copied the code which makes it possible to choose more than one item
> > > from a dropdown list in excel. Something I’m not clear on is, once the code
> > > is copied to the worksheet is that ability to pick more than one variable
> > > from a list valid for every column in the worksheet of just one column? I
> > > copied the code and it is only working for the column which was highlighted
> > > when I pasted the code. I need this to work for all my dropdown list within
> > > a single worksheet. Any help is so appreciated!!!!!!!!
> > >
> > >
> > >
|
Similar Threads
