Put Excel Data-Validation Selections on Separate Lines with VBA
Question details
Place multiple selections from an Excel data-validation list on separate lines within one cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses a VBA macro to retain multiple choices from a data-validation drop-down.
- Observed behavior
- The macro currently separates selections with commas; the desired result is one selection per line in the same cell.
Identify the cells that contain the data-validation drop-downs and confirm that you can edit and run VBA macros in your version of Excel. The example below uses B2:B100; change that range to match your worksheet.
Use a Worksheet_Change macro to add line breaks between selections
Use this approach to retain each new drop-down selection and display it on a separate line in the same cell.
The event procedure stores the new selection, uses Undo to retrieve the previous cell value, and joins the two with Chr(10). It then enables Wrap Text and restores events.
Right-click the worksheet tab containing the drop-downs and select View Code. If the sheet already has a Worksheet_Change procedure, update that procedure instead of adding a second one.
Paste the following code into the worksheet's code window and replace B2:B100 with your drop-down range: Private Sub Worksheet_Change(ByVal Target As Range) Dim newValue As String Dim oldValue As String If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B100")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub newValue = Target.Value On Error GoTo RestoreEvents Application.EnableEvents = False Application.Undo oldValue = Target.Value If oldValue = "" Then Target.Value = newValue Else Target.Value = oldValue & Chr(10) & newValue End If Target.WrapText = True Target.EntireRow.AutoFit RestoreEvents: Application.EnableEvents = True End Sub
Select File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros when prompted, then choose two items in a cell within the specified range.

Work with Spreadsheet Line Breaks in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move between office suites. It does not repair the Excel VBA event macro described above.

Frequently Asked Questions
Why do the selections appear on one line?
Make sure the macro joins values with Chr(10) and sets Target.WrapText = True. You can also select the cell and enable Wrap Text from Excel's Home tab.
Where should I put the Worksheet_Change code?
Right-click the tab of the worksheet containing the drop-downs, select View Code, and add the procedure to that worksheet's code window.
Why does the macro not run when I choose an item?
Check that the selected cell is inside the range specified in Me.Range, save the workbook as .xlsm, and enable macros when Excel prompts you. Also check that application events are enabled.
Can I use this macro for more than one drop-down cell?
Yes. Change Me.Range("B2:B100") to the worksheet range containing the drop-downs. The example handles one changed cell at a time.




