Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic line break in cells
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic line break in cells
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 -- Gary''s Student - gsnu200807 "vertblancrouge" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic line break in cells
Thanks a lot, does exactly what I need.
"Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200807 "vertblancrouge" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic line break in cells
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200807 "vertblancrouge" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic line break in cells
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 On Wed, 5 Nov 2008 07:56:04 -0800, vertblancrouge wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200807 "vertblancrouge" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot move an automatic page break | Excel Discussion (Misc queries) | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
concantenatring 2 cells with with a line break | Excel Discussion (Misc queries) | |||
How do I get a line break when using a formula to combine cells | Excel Worksheet Functions | |||
I need to print cells under the orginal line instead of page break | Excel Discussion (Misc queries) |