Move to Next cell AFTER certain # of characters are entered.

M

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi All:

I seek the knowledge of the experts once again...

Can you tell me if it is possible to have the active cell change after a certain number of characters are entered?

I have a spreadhseet that people enter a 36 digit code into. The different fields have a different amount of characters in them. The string is as follows:

J17 can enter 3 characters
K17 can enter 6 characters
L17 can enter 4 characters
M17 can enter 6 characters
N17 can enter 6 characters
O17 can enter 4 characters
P17 can enter 4 characters
Q17 can enter 4 characters

SO what I would like to happen is the user enters the 3 digits in J17 and then the active cell becomes K17 where they enter 6 digits and then the active cell becomes L17... Does this make sense? Currently the user has to Tab to the next cell but if they could enter the number contu=inous that would be great.

Any ideas?

THANKS,
Mark
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Enter the 36 character number in H17, then use a combination of left, mid & right to split out the 36 digit into the target cells.
 
Upvote 0
pretty sure that is not possible, but you can let the user enter the Entire String into 1 cell, and use formulas to extract each section to individual cells

let user enter entire string in A1

B1 formula =left(a1,3) Get's characters 1-3
C1 formula =mid(a1,4,3) Get's characters 4-7
D1 formula =mid)a1,8,5) Get's characters 8-12

etc...
 
Upvote 0
THANKS...

Hi:

THANKS to both of you for your assitance. I knew I could set up a onwe cell entry that would feed the others but I was hoping that VBA or Excel could do what I wanted. I guess unless someone else out there know a way to fulfill my request then I will put it back to the users to decide how they want to enter.

THANKS Again,
Take Care,
Mark
 
Upvote 0
Sorry, it is not possible to monitor how many characters are in a cell WHILE it is being edited.

However, you can do this if you use Text Boxes...

Create 2 textboxes on your sheet using the control toolbox (right click toolbar, select control toolbox).

once that is done, right click the 1st one, view code

enter this code
Code: 
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Len(TextBox1) = 3 Then TextBox2.Activate
End Sub

Enter 4 characters into the 1st textbox, the cursor now moves to the 2nd textbox

HTH
 
Upvote 0
Hi jonmo1:

THANKS for that suggestion as well as your other. I had not thought of Text Boxes. I may play around with that option later to see but currently I do not want to have to change the layout of my Input Sheet. I appreciate the options you gave me and will try them out when I get a chance. I can definately use the Text Box thing in the future...

Bye 4 Now,
Mark
 
Upvote 0
Awesome,

How would you put a limit on the number of chars a user can enter in the text box?

Thanks
 
Upvote 0
the bolded # controls that - user can enter 1 character more than that number

If Len(TextBox1) = 3 Then TextBox2.Activate

Len is a function that returns the length of a string.

basically, each time a key is pressed (before it adds the value of the key that was pressed) IN Textbox 1, it checks the length of the string in the textbox. If it is 3, it moves to textbox 2.

you'll have to repeat the code relative to each textbox..
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
179
Ark68
A
F
  • Solved
Vba loop sequence
Replies
4
Views
207
Frankroger
F
M
  • Solved
Using ActiveCell to Enter Data, Sort, and Move Cells.
Replies
9
Views
539
johnnyL
J
F
  • Question
Formula Help: Summing numbers from text cells, that occur before/after specific characters
Replies
3
Views
555
ExceLoki
ExceLoki
D
  • Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
708
davidepstein22
D

Forum statistics

Threads
1,215,045
Messages
6,122,836
Members
449,096
Latest member
Erald

Share this page

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