Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
I know to insert a new paragraph within the same cell to use the keystroke
alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
Paragraphs in cells? Excel is not a word-processing app!
Should you be using tables in Word? best wishes "charlie54933" wrote in message ... I know to insert a new paragraph within the same cell to use the keystroke alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
Not without using code.
You cannot do it while entering the text string because code won't run while in editing mode. You could do it after entry with code if you trained her to use a particular character where she wanted a new paragraph. Upon her hitting Enter key the event code would run to substitute the character with a linefeed. Chr(91) is the [ character. Private Sub Worksheet_Change(ByVal Target As Range) With Target .Replace what:=Chr(91), replacement:=Chr(10), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Personally I think Alt + Enter is just as easy while she is entering the text. Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 11:11:01 -0800, charlie54933 wrote: I know to insert a new paragraph within the same cell to use the keystroke alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
Hi,
I am trying to achieve the same thing for an Excel document that will be used by a huge audience within our organisation, and I am confident that there will be much resistance to using Alt+ENTER. I have managed to intercept the ENTER keypress whilst editing the cell with: Application.OnKey "~", "InCellReturn" (with InCellReturn being a custom Subroutine) However, I can't work out how to determine where the cursor is within the cell in order to insert the carriage return in the right place, and then return to edit mode. I also tried SendKeys in the Sub, but this unfortunately creates an infinite loop. Cheers, Ivan. "Gord Dibben" wrote: Not without using code. You cannot do it while entering the text string because code won't run while in editing mode. You could do it after entry with code if you trained her to use a particular character where she wanted a new paragraph. Upon her hitting Enter key the event code would run to substitute the character with a linefeed. Chr(91) is the [ character. Private Sub Worksheet_Change(ByVal Target As Range) With Target .Replace what:=Chr(91), replacement:=Chr(10), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Personally I think Alt + Enter is just as easy while she is entering the text. Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 11:11:01 -0800, charlie54933 wrote: I know to insert a new paragraph within the same cell to use the keystroke alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
Almost there... Here is the code now in the custom Sub:
Sub InCellReturn() Dim EditRng As Range Set EditRng = Selection.Cells(1) EditRng.Value = EditRng.Value & Chr(10) Application.SendKeys "{F2}" End Sub This works in conjunction with the Application.OnKey as long as the cursor is at the end of the cell text. However, it is still no good if someone has the cursor part way through the text. Cheers, Ivan. "Ivan Camac" wrote: Hi, I am trying to achieve the same thing for an Excel document that will be used by a huge audience within our organisation, and I am confident that there will be much resistance to using Alt+ENTER. I have managed to intercept the ENTER keypress whilst editing the cell with: Application.OnKey "~", "InCellReturn" (with InCellReturn being a custom Subroutine) However, I can't work out how to determine where the cursor is within the cell in order to insert the carriage return in the right place, and then return to edit mode. I also tried SendKeys in the Sub, but this unfortunately creates an infinite loop. Cheers, Ivan. "Gord Dibben" wrote: Not without using code. You cannot do it while entering the text string because code won't run while in editing mode. You could do it after entry with code if you trained her to use a particular character where she wanted a new paragraph. Upon her hitting Enter key the event code would run to substitute the character with a linefeed. Chr(91) is the [ character. Private Sub Worksheet_Change(ByVal Target As Range) With Target .Replace what:=Chr(91), replacement:=Chr(10), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Personally I think Alt + Enter is just as easy while she is entering the text. Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 11:11:01 -0800, charlie54933 wrote: I know to insert a new paragraph within the same cell to use the keystroke alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
Macros that do anything significant won't run if the user is in edit mode.
Maybe you can tell the user to use a specific character (like |) that isn't used anywhere else in the workbook. Then you could use a Workbook_SheetChange (or an application event for lots of workbooks???) to change this unique character to the alt-enter. Personally, I think you're underestimating your users. They will learn how to use it--and maybe use it in workbooks that you don't know about. Ivan Camac wrote: Almost there... Here is the code now in the custom Sub: Sub InCellReturn() Dim EditRng As Range Set EditRng = Selection.Cells(1) EditRng.Value = EditRng.Value & Chr(10) Application.SendKeys "{F2}" End Sub This works in conjunction with the Application.OnKey as long as the cursor is at the end of the cell text. However, it is still no good if someone has the cursor part way through the text. Cheers, Ivan. "Ivan Camac" wrote: Hi, I am trying to achieve the same thing for an Excel document that will be used by a huge audience within our organisation, and I am confident that there will be much resistance to using Alt+ENTER. I have managed to intercept the ENTER keypress whilst editing the cell with: Application.OnKey "~", "InCellReturn" (with InCellReturn being a custom Subroutine) However, I can't work out how to determine where the cursor is within the cell in order to insert the carriage return in the right place, and then return to edit mode. I also tried SendKeys in the Sub, but this unfortunately creates an infinite loop. Cheers, Ivan. "Gord Dibben" wrote: Not without using code. You cannot do it while entering the text string because code won't run while in editing mode. You could do it after entry with code if you trained her to use a particular character where she wanted a new paragraph. Upon her hitting Enter key the event code would run to substitute the character with a linefeed. Chr(91) is the [ character. Private Sub Worksheet_Change(ByVal Target As Range) With Target .Replace what:=Chr(91), replacement:=Chr(10), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Personally I think Alt + Enter is just as easy while she is entering the text. Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 11:11:01 -0800, charlie54933 wrote: I know to insert a new paragraph within the same cell to use the keystroke alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! . -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
Dave
The event code I posted uses the [ Chr(91) as the de-limiter that gets substituted by Chr(10) Apparently that would be too difficult for users. Gord On Fri, 29 Jan 2010 07:55:21 -0600, Dave Peterson wrote: Macros that do anything significant won't run if the user is in edit mode. Maybe you can tell the user to use a specific character (like |) that isn't used anywhere else in the workbook. Then you could use a Workbook_SheetChange (or an application event for lots of workbooks???) to change this unique character to the alt-enter. Personally, I think you're underestimating your users. They will learn how to use it--and maybe use it in workbooks that you don't know about. Ivan Camac wrote: Almost there... Here is the code now in the custom Sub: Sub InCellReturn() Dim EditRng As Range Set EditRng = Selection.Cells(1) EditRng.Value = EditRng.Value & Chr(10) Application.SendKeys "{F2}" End Sub This works in conjunction with the Application.OnKey as long as the cursor is at the end of the cell text. However, it is still no good if someone has the cursor part way through the text. Cheers, Ivan. "Ivan Camac" wrote: Hi, I am trying to achieve the same thing for an Excel document that will be used by a huge audience within our organisation, and I am confident that there will be much resistance to using Alt+ENTER. I have managed to intercept the ENTER keypress whilst editing the cell with: Application.OnKey "~", "InCellReturn" (with InCellReturn being a custom Subroutine) However, I can't work out how to determine where the cursor is within the cell in order to insert the carriage return in the right place, and then return to edit mode. I also tried SendKeys in the Sub, but this unfortunately creates an infinite loop. Cheers, Ivan. "Gord Dibben" wrote: Not without using code. You cannot do it while entering the text string because code won't run while in editing mode. You could do it after entry with code if you trained her to use a particular character where she wanted a new paragraph. Upon her hitting Enter key the event code would run to substitute the character with a linefeed. Chr(91) is the [ character. Private Sub Worksheet_Change(ByVal Target As Range) With Target .Replace what:=Chr(91), replacement:=Chr(10), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Personally I think Alt + Enter is just as easy while she is entering the text. Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 11:11:01 -0800, charlie54933 wrote: I know to insert a new paragraph within the same cell to use the keystroke alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
I didn't scroll through the previous messages to see the entire thread. Sorry
for essentially duplicating your response. I still think that the OP is underestimating the user community. Gord Dibben wrote: Dave The event code I posted uses the [ Chr(91) as the de-limiter that gets substituted by Chr(10) Apparently that would be too difficult for users. Gord On Fri, 29 Jan 2010 07:55:21 -0600, Dave Peterson wrote: Macros that do anything significant won't run if the user is in edit mode. Maybe you can tell the user to use a specific character (like |) that isn't used anywhere else in the workbook. Then you could use a Workbook_SheetChange (or an application event for lots of workbooks???) to change this unique character to the alt-enter. Personally, I think you're underestimating your users. They will learn how to use it--and maybe use it in workbooks that you don't know about. Ivan Camac wrote: Almost there... Here is the code now in the custom Sub: Sub InCellReturn() Dim EditRng As Range Set EditRng = Selection.Cells(1) EditRng.Value = EditRng.Value & Chr(10) Application.SendKeys "{F2}" End Sub This works in conjunction with the Application.OnKey as long as the cursor is at the end of the cell text. However, it is still no good if someone has the cursor part way through the text. Cheers, Ivan. "Ivan Camac" wrote: Hi, I am trying to achieve the same thing for an Excel document that will be used by a huge audience within our organisation, and I am confident that there will be much resistance to using Alt+ENTER. I have managed to intercept the ENTER keypress whilst editing the cell with: Application.OnKey "~", "InCellReturn" (with InCellReturn being a custom Subroutine) However, I can't work out how to determine where the cursor is within the cell in order to insert the carriage return in the right place, and then return to edit mode. I also tried SendKeys in the Sub, but this unfortunately creates an infinite loop. Cheers, Ivan. "Gord Dibben" wrote: Not without using code. You cannot do it while entering the text string because code won't run while in editing mode. You could do it after entry with code if you trained her to use a particular character where she wanted a new paragraph. Upon her hitting Enter key the event code would run to substitute the character with a linefeed. Chr(91) is the [ character. Private Sub Worksheet_Change(ByVal Target As Range) With Target .Replace what:=Chr(91), replacement:=Chr(10), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Personally I think Alt + Enter is just as easy while she is entering the text. Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 11:11:01 -0800, charlie54933 wrote: I know to insert a new paragraph within the same cell to use the keystroke alt-enter, however, I'm trying to make a usable form for my boss, and I'd like to make it super user friendly and not have to have her use alt-enter every time she needs to create a new paragraph in one particular cell. Any way to use some kind of code to allow the new paragraph in the same cell at just hitting the enter key? Thank you!! . -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
I agree.
I think teaching the two-finger Alt + Enter is better than cluttering up with event code in any case. Who knows?............a change event may be required for some other operation on same sheet. Gord On Fri, 29 Jan 2010 11:26:22 -0600, Dave Peterson wrote: I still think that the OP is underestimating the user community. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
I don't like things that look too much like magic <vbg.
And I hate it when the contents of my clipboard is gone, too! Gord Dibben wrote: I agree. I think teaching the two-finger Alt + Enter is better than cluttering up with event code in any case. Who knows?............a change event may be required for some other operation on same sheet. Gord On Fri, 29 Jan 2010 11:26:22 -0600, Dave Peterson wrote: I still think that the OP is underestimating the user community. -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
On 1/29/2010 9:26 AM, Dave Peterson wrote:
I didn't scroll through the previous messages to see the entire thread. Sorry for essentially duplicating your response. I still think that the OP is underestimating the user community. Maybe so, but the OP said it was for his boss. Perhaps he knows the boss's computer limitations better than we do... OTOH, alt-Enter shouldn't be that difficult to learn. Bill |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Carriage return in same cell without using alt-enter
If the boss can't grasp this concept, then he/she shouldn't be a boss--or the OP
shouldn't be working at a company that would have that person as a boss. Bill Sharpe wrote: On 1/29/2010 9:26 AM, Dave Peterson wrote: I didn't scroll through the previous messages to see the entire thread. Sorry for essentially duplicating your response. I still think that the OP is underestimating the user community. Maybe so, but the OP said it was for his boss. Perhaps he knows the boss's computer limitations better than we do... OTOH, alt-Enter shouldn't be that difficult to learn. Bill -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting rid of carriage return in cell | Excel Discussion (Misc queries) | |||
Carriage return within a cell | Excel Discussion (Misc queries) | |||
Can I put a carriage return in an EXCEL cell | Excel Discussion (Misc queries) | |||
Alt+enter to carriage return within a cell does not work...? | Excel Discussion (Misc queries) | |||
How can I enter carriage returns into text within a cell in excel | Excel Discussion (Misc queries) |