Multi Select from Drop List
 

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

--
Thank you

Multi Select from Drop List
 

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



On Tue, 8 Sep 2009 04:55:02 -0700, PatriciaT
wrote:

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

Multi Select from Drop List
 

Thank you. I'll give it a try.


"Gord Dibben" wrote:

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



On Tue, 8 Sep 2009 04:55:02 -0700, PatriciaT
wrote:

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