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

[Mister H]

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

 

[SteveO59L]

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.

 

[Jonmo1]

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...

 

[Mister H]

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

 

[Jonmo1]

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

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

 

[Mister H]

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

 

[kyrgan]

Awesome,

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

Thanks

 

[Jonmo1]

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..