Forcing cursor to next cell

[Pastor Hank]

I'm new to Excel, so I have no idea if this can even be done....my church
secretary has a worksheet with three active cells, she wants to set the width
of C2 at 50 characters and when she's typing in that cell, if the text goes
over 50 characters, she wants to cursor to automatically jump to c3. (Just
go one down) Is this possible?

Thanks

 

[Otto Moehrbach]

No. Excel doesn't monitor an entry until it is entered. In other words,
Excel won't know the entry is over 50 characters until the entry is made.
What you can do is use a Worksheet_Change event macro that looks at the
entry after it has been made and, if it is over 50 characters, remove all
characters after 50 and place them in the cell below. Post back if this
will work for you and you need help with it. HTH Otto

 

[Pastor Hank]

Yes that would work, and yes I will definetly need help, I can barely create
Word macros, and this is the 2nd or 3rd time, I've even tried to do anything
with Excel...

thank you

 

[Otto Moehrbach]

I gather that you don't want the text to be broken in the middle of a word,
so you want to move the last word that takes the count over 50 to be moved
as well as everything after it. Is that right? Otto

 

[Pastor Hank]

Yes, and thank you for taking the time to help me with this.

Hank

 

[Otto Moehrbach]

These 2 macros will do what you want. Note that the first macro is a sheet
event macro and must be placed in the sheet module of your sheet. The
second macro must be placed in a regular module. I assumed that the entries
are made in Column A. If your column is other than Column A, then change
the "1" in the following line of the first macro to the column number you
wish:
"If Target.Column = 1 Then"
If you wish, send me an email with your email address and I'll send you the
small file I used for this. It will have all this code properly placed. My
email address is (e-mail address removed). Remove the "nop" from this address.
HTH Otto

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 1 Then
If Len(Target) < 51 Then
Exit Sub
Else
Call Move50(Target)
End If
End If
End Sub

Sub Move50(TheCell As Range)
Dim c As Long
For c = 1 To 50
If Not InStr(Right(TheCell, c), " ") = 0 Then Exit For
Next c
Application.EnableEvents = False
TheCell.Offset(1).Value = Right(TheCell, c - 1)
TheCell.Value = Left(TheCell, Len(TheCell) - c)
Application.EnableEvents = True
End Sub