Automatically go to the next cell below

BenNV

Board Regular
Joined
Mar 27, 2002
Messages
160
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.

_________________<A HREF= "http://website.lineone.net/~s-o-s/Index.html">
image001.gif

This message was edited by s-o-s on 2002-05-10 05:54
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top