Multi Select from Drop List

  • Thread starter Thread starter PatriciaT
  • Start date Start date
P

PatriciaT

Good moring, i've researched and found link to sample file below. Was able
to implement. There are some cells however where i do want to allow mutliple
selections from drop down lists and other cells where i do not. Can anyone
tell me how to execute this code while in designated cells only? Thanks.
http://www.contextures.com/excelfiles.html
 
Assuming you are working with Debra's workbook DataValMultiSelect.xls and
sheet named "SameCell" with DV dropdowns in C3:C7

Revised event code to allow multiselection in cells C6 and C7 only. Adjust
to suit.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const rngDV As String = "C3:C5"
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
On Error GoTo exitHandler
If Range(rngDV) Is Nothing Then GoTo exitHandler
If Not Intersect(Target, Range(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


Gord Dibben MS Excel MVP
 
Thank you. I'll give it a try.


Gord Dibben said:
Assuming you are working with Debra's workbook DataValMultiSelect.xls and
sheet named "SameCell" with DV dropdowns in C3:C7

Revised event code to allow multiselection in cells C6 and C7 only. Adjust
to suit.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const rngDV As String = "C3:C5"
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
On Error GoTo exitHandler
If Range(rngDV) Is Nothing Then GoTo exitHandler
If Not Intersect(Target, Range(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


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top