Prev: use Find without hardcoded value but a value from a cell
Next: Excel worksheets won't all print
From: charlie54933 on 5 Jan 2010 14:11
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!!
From: Bernard Liengme on 5 Jan 2010 15:41
Paragraphs in cells? Excel is not a word-processing app!
Should you be using tables in Word?
best wishes

"charlie54933" <charlie54933(a)discussions.microsoft.com> wrote in message
news:E798F409-2614-4B80-851C-FEB817A63A18(a)microsoft.com...
> 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!!

From: Gord Dibben on 5 Jan 2010 19:05
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
<charlie54933(a)discussions.microsoft.com> 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!!

From: Ivan Camac Ivan on 28 Jan 2010 23:14
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
> <charlie54933(a)discussions.microsoft.com> 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!!
>
> .
>
From: Ivan Camac Ivan on 28 Jan 2010 23:31
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
> > <charlie54933(a)discussions.microsoft.com> 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!!
> >
> > .
> >
 |  Next  |  Last
Pages: 1 2 3
Prev: use Find without hardcoded value but a value from a cell
Next: Excel worksheets won't all print