Hi -
using Excel 2007 -
I have a drop-down list and running the following VB Code in the back. The VB Code help me to select multiple choices in each cell based on the list. Each selection is separated by comma which is ok but I would like to display each selection as one line in a same cell. The code below is applying on two columns 15 and 20 and i would like to format only 15, if possible. How is that achievable?
List selection in one cell showing up as:
one, two, three, four,
five, six, seven
I want to display the list in one cell as:
one
two
three
four
five
six
seven
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 = 15 Or Target.Column = 20 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
Please advice.
Regards
using Excel 2007 -
I have a drop-down list and running the following VB Code in the back. The VB Code help me to select multiple choices in each cell based on the list. Each selection is separated by comma which is ok but I would like to display each selection as one line in a same cell. The code below is applying on two columns 15 and 20 and i would like to format only 15, if possible. How is that achievable?
List selection in one cell showing up as:
one, two, three, four,
five, six, seven
I want to display the list in one cell as:
one
two
three
four
five
six
seven
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 = 15 Or Target.Column = 20 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
Please advice.
Regards