Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Automatically go to the next cell below

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Code:
    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •