Reach Character Limit then move to next cell

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
Is there a way to automatically select the next unlocked cell when the cell before it reaches its character limit.

For example, if I put an X in a cell, I want the next cell to be selected without having to TAB or select it myself.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
Based on your example, here's a possible workaround. Place this in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Activate
Select Case ActiveCell.Value
Case ""
Exit Sub
Case "X"
ActiveCell.Offset(1, 0).Activate
Do Until ActiveCell.Locked = False
If ActiveCell.Locked = True Then
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Select
End If
Loop
Case Else
MsgBox "Value not permitted.", 16, "Only one character ''X'' allowed."
ActiveCell.Value = ""
Exit Sub
End Select
End Sub

This can be modified for range, and length of value too, but if you only want one character and it's an X, this might do the job.

Be careful of one thing, that is to provide for an unlocked cell in your range (or elsewhare on the sheet if no specific range is coded). If there isn't such a cell, you may need to add an error trap to the code. If you always arrange for another unlocked cell somewhere, then this code may suffice as is.

Any help?
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
This is interesting code but not what I'm looking for. I'm going to play with it to see if I can get it to do what I want...I'll report the results.
 

Forum statistics

Threads
1,147,748
Messages
5,742,978
Members
423,769
Latest member
LongToast

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
Top