Solved

Controlling the position in Protected Worksheet

Posted on 2013-11-05
5
197 Views
Last Modified: 2013-11-09
EE Pros,

I have protected a sheet that has a number of cells that allow for imputting data.  The cells that you input data into are unprotected so as to allow one to navigate from cell to cell and input the data.  Here's the question;  When you go from one cell to the other, it doesn't go in the correct cell to cell path.  Instead, it may go to another unprotected cell further down and not in the natural order.  How do you control the cursor to go to the next logical cell?

Thank you in advance,

B.
0
Comment
Question by:Bright01
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39626301
The normal tab order should be Left to Right, then Down.

If you want to change this, you can use a Macro.

For instance:

Sub SetTabKey()
'sets the arrow and tab keys
Application.OnKey "{TAB}", "NextCellSelect"
End Sub

Sub NextCellSelect()
'Go to next cell in pseudo-tab-sequence
Select Case Selection.Address
Case [cell].Address: [cell].Select
Case [cell].Address: [cell].Select
End Select
End Sub

Open in new window


Where [cell] is changed to suit your needs.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39627192
There is an option that can be enabled for what happens to the cursor when enter is pressed. If you have this enabled and the movement is set to Down then the movement would go down to the next available cell.

If however you use tab on entering data rather than enter I suspect the cursor would move right first.

You could also set the movement on Enter to Right.

Thanks
Rob H
0
 

Author Comment

by:Bright01
ID: 39627491
Thanks for the update.  Rob H. where is the option?  I don't think I can use a macro because I have a large number of unlocked cells I would have to identify inside the macro.  For whatever reason, it doesn't behave normally like down and right.

So perhaps the option approach is worth a try.  

B.
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39627538
Excel 2003 - Tools > Options

Excel 2007 - Big Button > Excel Options > Advanced

Excel 2010 - File tab > Options > Advanced

Thanks
Rob H
0
 

Author Closing Comment

by:Bright01
ID: 39635531
Thank you Rob!

B.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 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

20 Experts available now in Live!

Get 1:1 Help Now