automatically tab to next unprotected cell

Posted on 2012-09-21
Last Modified: 2013-09-03
I am looking for a way to automatically tab to the next unprotected cell after a single number is entered into a cell without using the tab, arrow, or enter keys.  Is that possible?

Example:  I have multiple rows data.  In columns E:M and O:W I would like to enter a single number and advance to the next cell in that row without using the tab, arrow, or enter keys.  Everything else in the worksheet is protected with formulas.

Thank you for any help.
Question by:thedunna
    LVL 26

    Expert Comment

    Hi, thedunna.

    I can't think of a way to do exactly what you describe. As a possible workaround, please see the attached. Key in a string of digits to A1. Then hit Enter (sorry!). At this point, the individual digits are output to range B1:B20. (I picked 20 arbitrarily - no reason why it shouldn't be more. BTW, if you enter more than 20 digits then the excess is dumped into B21.)

    Assuming that the above isn't acceptable, please give us some background as to what you'll be doing and why.

    LVL 44

    Expert Comment

    If you would allow an enter or tab after the digit, then you can accomplish this by selecting the unprotected cells.

    You can do this programmatically or manually.  You can see this by selecting non-adjacent cells with Ctrl+click and press the Enter key.

    The programmatic change of cell activation, based on a digit being typed is a more difficult coding challenge.  

    Is it possible for you to use a User Form for the data entry process? Using a textbox instead of a cell might provide an easier solution path.
    LVL 44

    Accepted Solution

    You can also use a textbox that isn't a user form control.  In the attached file, I have a multi-area range selected when Sheet1 is activated and the textbox control sits on top of the active cell. When a number is typed, the activecell is updated and the process repeats with the next cell in the selection.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now