VB Code ~ Formatting List cell

lexkhan

New Member
Joined
Mar 4, 2008
Messages
47
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,792
Try
Code:
Target.Value = oldVal & vbCr & newVal
Windows users should try
Code:
Target.Value = oldVal & vbLF & newVal
 

lexkhan

New Member
Joined
Mar 4, 2008
Messages
47
Try
Code:
Target.Value = oldVal & vbCr & newVal
Windows users should try
Code:
Target.Value = oldVal & vbLF & newVal

I am not VB Savy, can you please tell me where exactly should the code above be inserted?

Thank you,
lexkhan
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,792
Where the current code puts a comma between oldVal and newVal in the Target.Value, that line will put a line feed.
 

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top