Limit to 5 characters...
 

Dear all,

Is there any ways to limit the number of alphabets you key in the cell and
move the to the next cell? For example if you enter "circular" which has 8
characters, is there a way such that:
A B C
1 circu
2 lar
3

Please help..... Thank you.

Limit to 5 characters...
 

Maybe you could tinker around with Data Validation
Under Allow, there's an option: Text length
to restrict inputs into cells
(there's no auto-overflow feature, though)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Astro" wrote:
Is there any ways to limit the number of alphabets you key in the cell and
move the to the next cell? For example if you enter "circular" which has 8
characters, is there a way such that:
A B C
1 circu
2 lar
3

Please help..... Thank you.

Limit to 5 characters...
 

You can't do what you want while the typing is taking place, but you can
when the user finishes typing (signaled by hitting Enter or taking an action
which moves the highlight to a new cell). Right click the tab at the bottom
of the worksheet you want this functionality for, select View Code from the
popup menu that appears and then copy/paste the following into the code
window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Delta As Long
Dim TargetText As String
If Not Intersect(Target, Columns("A")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
TargetText = Target.Value
Do While Len(TargetText)
Target.Offset(Delta).Value = Left(TargetText, 5)
Delta = Delta + 1
TargetText = Mid(TargetText, 6)
Loop
Target.Offset(Delta).Select
End If
Whoops:
Application.EnableEvents = True
End Sub

That's it. Go back to the worksheet and type something into any cell in
Column A.

--
Rick (MVP - Excel)


"Astro" wrote in message
...
Dear all,

Is there any ways to limit the number of alphabets you key in the cell and
move the to the next cell? For example if you enter "circular" which has 8
characters, is there a way such that:
A B C
1 circu
2 lar
3

Please help..... Thank you.