automatically tab to next unprotected cell

Posted on 2012-09-21
Medium Priority
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
  • 2
LVL 26

Expert Comment

ID: 38424750
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 46

Expert Comment

ID: 38424817
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 46

Accepted Solution

aikimark earned 2000 total points
ID: 38447136
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

840 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