Automatically go to the next cell below

[BenNV]

Is there a way in VBA to move to the next cell below once the data in the cell reaches the column width of 50?

So for example, if I type in cell B6 and the data goes beyond 50, then as soon as it reaches 50, it moves down one cell, in this case B7.

Thank you.

[s-o-s]

On 2002-05-10 02:10, BenNV wrote:
Is there a way in VBA to move to the next cell below once the data in the cell reaches the column width of 50?

So for example, if I type in cell B6 and the data goes beyond 50, then as soon as it reaches 50, it moves down one cell, in this case B7.

Thank you.

The attached code will do this. It does not work if you enter multiple rows eg select A3:B3 and Ctrl Enter. It picks this up as a probable error and traps it, you can then use undo before doing the cells individually.
ps The line TgtLen = 50 can be adjusted to allow any number of chars.

This code needs to be posted into the This workbook module.

Public Inprog As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Inprog = True Then GoTo Thend
On Error GoTo MultiRow
If Target.Value = "" Then GoTo Thend
On Error GoTo 0
TgtLen = 50
TgtVal = Target.Value
TgtAdd = Target.Address

TgtRow = 0
Inprog = True
While Len(TgtVal) > 0
  
    Range(TgtAdd).Offset(TgtRow, 0).Value = Left(TgtVal, TgtLen)
    If Len(TgtVal) < TgtLen Then
        TgtVal = ""
        GoTo Lenzero
    End If
    TgtVal = Right(TgtVal, Len(TgtVal) - TgtLen)
    TgtRow = TgtRow + 1
Lenzero:
Wend
Inprog = False
GoTo Thend
MultiRow:
Msg1 = "An error has occurred, have you Changed multiple rows? Macro has not run"
MsgBox Msg1

Thend:

End Sub

The change is only registered by Excel once you press return therefore it will not do this as you type!! only after you press return.

_________________



[ This Message was edited by: s-o-s on 2002-05-10 05:54 ]