Password protected sheet issues

[borgem]

I just ran into this issue now using excel 2007 and don't recall it being an issue with 2003. I have a protected worksheet and when I tab over a few cells and hit enter it takes me down one cell rather than to one cell down but in the same column as before. i.e. Before in 2003 if I started in A1 and tabbed over three times and was in D1, then hit enter, it would take me to A2. Now in 2007 if the worksheet is protected it will take me to D2 instead. Any suggestions?

 

[borgem]

*bump*

 

[schielrn]

I don't remember how it acted in 2003 as I don't have it anywhere to try it anymore. But I did notice while testing this, that if a cell was checked locked I couldn't get it to tab at all (i.e. all cells were locked) If I unlocked cells it would tab over the locked ones, but I had the box checked to select locked cells. Which I think is wierd. But if I tabbed over 3 times and then pressed enter it would go one cell below the original cell even if that cell was locked. I cannot replicate your issue, so I am not sure if it is a version issue or not.

I know I helped none with this, but I will see if I can get to a computer with 2003 and check the behaviour there.

 

[borgem]

You did you best. There are some hidden (and locked) cells w/ functions in them that I never mess with. Maybe those are doing something to prevent this. Thanks anyway

 

[borgem]

https://www.yousendit.com/download/VGljek9wMHc1aWJIRGc9PQ

Here is my sheet...it is currently protected (not by password)...the range A1:V26 seems to be locked somehow when protected. If you manually click outside of that range and enter data, this "invisible range" shifts to include that cell. For instance, if you double click into K32 and enter in data it will expand this "invisble range" to be A1:V32.

Again, please reference my initial problem. I would like to have cells with formulas protected so I can enter in the time in/time out and keep tabbing across and then hit enter and return the the next row down and first column. As it is, with it protected it just goes one cell down. Whatever is causing this "invisible range" must be the cause of my problems with this file.

It works fine unprotected...Here are my checks and solutions I have tried so far: there are no macros in the file and I checked for locked cells, unlocked everything and then only locked specific cells with formulas and the problem persists. I copied just the range I am using into a new sheet(A1:R11) and the new "Invisible Range" is A1:R11

any ideas? my only solution is to recreate the sheet by manually recreating the sheet, which is simple enough, but for future reference I want to know what went wrong with this file.

 

[albasheer]

It seems the problem of not returning to A2 after hitting enter is not file-specific. It happens whenever you protect the sheet and not have "select locked cells" checked. I am not sure about the A1:V26 range issue. I am still investigating that.

Now to solve the tab and enter issue, go to VB making sure that the sheet IS protected when you do so, and go to the properties window for the sheet. For "EnableSelection" change the setting to 0 -xlNoRestrictions. That should do it.

 

[albasheer]

Unfortunately, when you exit the file the settings are changed back. So it does not work. But maybe this info can help find the solution. I think the only remaining solution for now is to allow select locked cells. The user would be able to see the formula by clicking on it but would not be able to change it. Also the tab would skip over it.

 

[albasheer]

Try this:

Private Sub Workbook_Open()
Worksheets(1).EnableSelection = xlNoRestrictions
End Sub

 

[borgem]

Worked like a charm...not sure why I never thought to simply allow locked cells to be selected when protecting. I think my train of thought was that I didn't want it to be selected when I tabbed across them. I thought checking that option would make it so that when I tabbed over it would land on those cells as well. Failed reasoning. I appreciate all of your help