Auto enter in Excel

[akira01]

Hi All,
I need some help and guide for excel experts here. Is there a way that Excel can automatically enter to next row once the typing has reach the end.

Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E?

Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.

Please advise. Thanks in advance.

 

[Stormseed]

As far as I know, you cannot do that (dont know if VBA has some kind of function to do that because I have never come across this type of requirement). It shows you that the trext is going beyond F or G columns but if you enter the text in cell A1, the text remains in cell A1. You only visualize that it is jumping across columns.

Also, the options, "Shrink to fit" or "Wrap Text" would help you to a certain extent but not entirely. Moreover, you can explicitly press Alt+Enter but this would resize the row height.

 

[akira01]

Thanks for your prompt reply. In fact I will need to enter some text/number in column F, G and so on. If I enter something there, the whole text will break at row E. I know wrap text can do that but unfortunately sometime, I may need to enter some formulation between A to E.

Example:
A________B_______C_______D________E
Typing text here will go beyond A to E. right
Value : =sum(F2..F5)

Really hope there is a way to do the auto jump to next cell like Lotus 123 (older version) which I was using it many years ago.

Please advise. Thanks

 

[Stormseed]

What do you mean by auto jump ? Is it that you want to jump your cursor from A1 to F1 directly ?

 

[AnalyticsGuy7]

I have an interesting solution, but I'm not sure if it will work entirely.

The first thing you will need to figure out is the length of text that is TOO long (meaning it spills onto column F).

Then you would have the following Private Sub in that Worksheet...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim c, d As Range

Set d = Intersect(Target, Range("A:A"))
If d Is Nothing Then
Else
If Len(Target) > 49 Then
'MsgBox Target.Offset(1, 0)
Target.Offset(1, 0) = Right(Target, Len(Target) - 49) & Target.Offset(1, 0)
Target = Left(Target, 49)
End If
End If

End Sub

This is an example where 49 is the limit (anything over 49 needs to spill over).

All you need to do is type whatever you want in say... A1. Anything greater than the number of characters will spill over into A2 and then if A2 becomes past that limit, it will automatically spill over into A3, etc.

I hope this works!

 

[erik.van.geit]

Hi,

about the auto jump:
unlock the cells you want to fill in
(select columns A:E, menu format / cellproperties: last tab "secutiry": uncheck "locked"
protect your worksheet (password not needed)

when using tab you will get to column A when you have filled in column E

kind regards,
Erik

 

[akira01]

I have an interesting solution, but I'm not sure if it will work entirely.

Thanks for the tips. May I know where should I enter these code so that I can test it out.

Pls advise. Thanks

 

[akira01]

Dear AnalyticsGuy7,
I know how to enter the code already. It really works. You are great and thank you very very much. Thank you thank you.

BTW, is there any disadvantage with these code that I should know.

Once again, thanks for your help.

 

[akira01]

Hi, after trying your code on my actual document, I found a problem that I hope you are able to guide me on how to solve it. Sorry.

The code really works but it will break word when it reach the number of alphabet. For example, I set the 49 and the last word in that row is "anything", this word will end as "an" and "ything" appears in the row below. Obviously "a" is number 48 and "n" is 49 and that is why it break.

Is there a way to tweak the coded so that it base on word instead of number of characters.

Please advise. Thanks

 

[AnalyticsGuy7]

If I understand what you are saying correctly...

If the word "anything" begins on character 48, instead of having the "nything" go on the next line, you would want the "a" to come along with it, and leave the first rows at 47 characters.

That would require more work and thought...

I might be able to try something out and will try to get back to you...