Automatic line break in cells

[vertblancrouge]

Hi

I know I can perform a manual line break in a cell by pressing ALT+ENTER,
but is there a way to do this automatically after a space or a specific
caracter?

I get a report generated from another software and column A lists different
tags. There can be up to 10 different tags within one cell and all thos tags
are separated by a comma.

How can I tell Excel to automatically insert a line break after evey commas
in the cells of column A?

Thanks

 

[Gary''s Student]

This macro is coded for column A; adjust to suit your needs:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
Dim s1 As String
Dim s2 As String
s1 = ","
s2 = s1 & Chr(10)
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Value = Replace(t.Value, s1, s2)
Application.EnableEvents = True
End Sub

It goes in the worksheet code area. Because it is worksheet code, it is
very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[vertblancrouge]

Thanks a lot, does exactly what I need.

This macro is coded for column A; adjust to suit your needs:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
Dim s1 As String
Dim s2 As String
s1 = ","
s2 = s1 & Chr(10)
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Value = Replace(t.Value, s1, s2)
Application.EnableEvents = True
End Sub

It goes in the worksheet code area. Because it is worksheet code, it is
very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[vertblancrouge]

Hi again

I am in a situation where my Excel document is populated automatically by
another software. I want to achieve the results discussed above but when I
use the same macro I need to manually enter the cell and hit enter for the
macro to take action.

Is there a way to set the wprkbook to automatically apply the macro? It
didn't botterd me to much before but now I'm having reports of 500 lines and
more. It gets pretty long to do the "enters".

Thanks

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range
Dim t As Range
Set t = Target
Set a = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
Dim s1 As String
Dim s2 As String
s1 = ","
s2 = s1 & Chr(10)
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each t In a
t.Value = Replace(t.Value, s1, s2)
Next t
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP