+ Reply to Thread
Results 1 to 16 of 16

How to setup a cell to accept only one character then move to the next cell

  1. #1
    Registered User
    Join Date
    02-09-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    6

    How to setup a cell to accept only one character then move to the next cell

    Hello,
    This is my first post on here so sorry if this is posted in the wrong section but I figure you can never go wrong in general right? The question I have is I have an excel sheet that I want to use to enter survey results. We are using 1 - 5 for a scale as unsatisifed to extremely satisifed, some questions use 1 for yes 2 for no and 5 for no reply, and some use 1 for yes (there are four answers and the user can only select one). The question I have is there will only ever be one character entered into a cell is there anyway to tell that there has been a character entered and automatically move to the next cell (downward)?
    TIA

  2. #2
    Forum Contributor
    Join Date
    01-24-2011
    Location
    Sheppey
    MS-Off Ver
    Excel 2010
    Posts
    239

    Cool Re: How to setup a cell to accept only one character then move to the next cell

    If you want to make sure that only a number from 1 - 5 is used in a cell then use data validation and make a drop down box using the list option.

  3. #3
    Registered User
    Join Date
    02-09-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to setup a cell to accept only one character then move to the next cell

    Yes I have setup the validation to only allow the certain input. That is not what I am trying to do however. I need to make it so that once a user types in one of the values it automatically goes to the next cell. For example there are 14 questions lets just say a1 through a15. After entering a 1 in cell A1 is there a way to make the cursor automatically jump to A2 and so on?

  4. #4
    Forum Contributor
    Join Date
    01-24-2011
    Location
    Sheppey
    MS-Off Ver
    Excel 2010
    Posts
    239

    Cool Re: How to setup a cell to accept only one character then move to the next cell

    I do not believe so, excel does not know when you have finished entering and besides how difficult is it to press the return button anyway.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to setup a cell to accept only one character then move to the next cell

    I can provide code (VBA) to do this if it is only digits 0 through 9, one character per cell, and restricted to a single range. Have you used VBA?
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    02-09-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to setup a cell to accept only one character then move to the next cell

    Barry there is no reason to get smart. It is that hard when you have to press enter thousands of times for what I am doing. Shg thank you for the reply it is only digits 0-9 and yes I have used vba. Thank you

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to setup a cell to accept only one character then move to the next cell

    In the Sheet module:

    Please Login or Register  to view this content.
    In a code module:

    Please Login or Register  to view this content.
    Change the constants iCol and lCol as needed.

  8. #8
    Registered User
    Join Date
    02-09-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to setup a cell to accept only one character then move to the next cell

    Hi shg,
    Thank you for the reply. I have placed the first part of the code on my Sheet1 module and placed the other code in a code module under modules. When entering a digit into the cell nothing happens. Am I missing something? All of my coding in VBA has been with controls (check boxes, control buttons etc) I have never had anything automatic like this so I may be missing something.
    TIA

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to setup a cell to accept only one character then move to the next cell

    When entering a digit in what cell? As written, it works in columns C to I.

    Are macros enabled?

  10. #10
    Registered User
    Join Date
    02-09-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to setup a cell to accept only one character then move to the next cell

    Hi shg,
    I got it working (macros were disabled in excel). The only problem is that your code moves the cursor to the right I need it to go down. Thank you so much for your help. Also this seems to have changed my data validation. On some questions the answer is only a 1 on others 1 - 5 and on others a 1,2, or 5. For example

    c3-c6 can only contain a 1
    c7 - c18 can contain 1 - 5
    and c19 can conatin a 1,2 or 5

    Does this need to now be programmed into this code for data validation. I appreciate your help and I am getting into a lot more Excel programming and will contribute back to the forums if I can.
    Last edited by ewm250; 02-11-2011 at 02:06 PM.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to setup a cell to accept only one character then move to the next cell

    Change

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    02-09-2011
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to setup a cell to accept only one character then move to the next cell

    Thank you shg. It is working now with a few exceptions.

    1. it seems to have broken my data validation.
    2. Now when trying to change a value, say I realize that the value in cell b6 is wrong and I am in f6 I cannot use the up arrow key as it erases the values. If I click in b6 and type my correct value I must then rekey all values after that as it erases them.
    3. I cannot use the numbers on the keypad, it will only accept the numbers above the letters. It looks like the code is using ascii characters which is weird why it would see them as different.

    I am so sorry to be a pain and I am very appreciative of your help.

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to setup a cell to accept only one character then move to the next cell

    1. it seems to have broken my data validation.
    That's because the values are entered via code, which is not subject to data validation. You could use conditional formatting to highlight invalid entries.

    It also flushes the Undo stack.
    as it erases the values.
    Changed below.
    cannot use the numbers on the keypad, it will only accept the numbers above the letters.
    OnKey and the numeric keypad are not friendly. Keycodes 95 to 104 work on some PCs but not others. I'm sure it could be cobbled with Windows API (anything can be), but I have no solution.

    Please Login or Register  to view this content.
    Last edited by shg; 02-13-2011 at 03:28 PM.

  14. #14
    Registered User
    Join Date
    09-05-2014
    Location
    Wirral
    MS-Off Ver
    2010
    Posts
    1

    Unhappy Re: How to setup a cell to accept only one character then move to the next cell

    Hi shg,

    I found this code that you posted a few years back and it's been a real help, I'm a novice to VBA. While this allows you to enter numbers and then move to the next cell, it doesn't seem to work with letters! Is this possible. Any help would be fantastic and thank you in advance.

  15. #15
    Registered User
    Join Date
    11-30-2014
    Location
    turkey
    MS-Off Ver
    2010
    Posts
    2

    Re: How to setup a cell to accept only one character then move to the next cell

    hi shg;
    I was not able to use teh code.
    There is a "runtime error 424-object required" when i click run. When i debug, it shows the line "If .Count > 1 Then" as yellow.
    So can please explain how to do this in detail again since i dont know much about makros or can you create an excel page with this makro and send it to me (my mail), that would be really help. (anyone can also help )tx.

  16. #16
    Registered User
    Join Date
    11-30-2014
    Location
    turkey
    MS-Off Ver
    2010
    Posts
    2

    Re: How to setup a cell to accept only one character then move to the next cell

    anyone help please?
    I was not able to do this.
    Can you send the excel file with the specified makro to [email protected]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1