[RESOLVED] [Excel] Help! Up and down arrow keys aren't working in locked spreadsheet

[Dynamo Nath]

I have two identical (at least as far as I can tell!) spreadsheets. One is a copy of the other that I use to test out stuff before turning it live. In the test one, one it is locked the arrow keys work moving the cursor around the unlocked cells as I would expect. However, in the live version, the heavy black border around the active cell disappears and the up/down arrow keys do not work. Running one of the macros causes the black border to re-appear but does not return up/down functionality. Help, I'm stumped!

[Siddharth Rout]

...Running one of the macros causes the black border to re-appear but does not return up/down functionality...

I think I know where the problem lies but I need to see the sheet before I comment....

[Dynamo Nath]

Sid to the rescue again :-)

How much detail do you need in the spreadsheet?

[Siddharth Rout]

Things that I want to check is

1) How are you protecting the sheet (permissions allowed)
2) Check the VBA code

Guess I will have to see the complete sheet...

[Dynamo Nath]

Here you go. I've taken out some of the info but that shouldn't effect the code etc. There's no protection on the sheet at the moment but I protected sheet and workbook, allowing filtering, hyperlinks and select unprotected cells.

[Siddharth Rout]

However, in the live version, the heavy black border around the active cell disappears and the up/down arrow keys do not work.

is this file the live version?

[Dynamo Nath]

yes it is. or rather, it's a copy of it which I removed some data from.

[Siddharth Rout]

the heavy black border around the active cell disappears

Ok I have the file opened in front of me.... now which cell are we talking about?

[Dynamo Nath]

Knowing my luck it's probably working properly for you. You normally get a black border around the active cell that you're working in. For us that seems to have vanished on this spreadsheet...

[Siddharth Rout]

what is your zoom %age? when you open the file?

[Dynamo Nath]

100% as we're using 22" widescreen monitors

[Siddharth Rout]

I can see the black borders...

Can you post a screenshot of what you see?

[Dynamo Nath]

Hmm, to my embarassment, the black border has returned this morning (thankfully we're getting new machines soon!!!), but am still unable to arrow up and down; just left and right. Sorry for wasting your time.

[Siddharth Rout]

Sorry for wasting your time.

Don't blame your self for this

I am always ready for a challenge

but am still unable to arrow up and down; just left and right. Sorry for wasting your time.

Can you be more specific... which cell is highlighted when you press the up arrow key and down? Is scroll lock off?

[Siddharth Rout]

ok wait... I understand.... give me a moment...

Ok Got it

When you are protecting the worksheet, also select the option "Select Locked cells" and then put the password...

now try it

[Dynamo Nath]

I can now move the cursor around freely on a row but if I want to move to another row I have to click the mouse still; arrow keys still not working.

[Siddharth Rout]

I can move the arrow key anywhere I want...

Did you do what I suggested in my last post.

See snapshot attached...

[Dynamo Nath]

Have now done this and it works. However, the two files are now not consistent with each other as this box is only ticked in the one file. In my non-working copy you can only move around, in all directions, the unlocked cells.

[Siddharth Rout]

So set it in that as well... Or am I missing a point?

[Dynamo Nath]

Sort of. Why do I need to set it in one but not the other for it to work properly?

[Siddharth Rout]

Okay can you upload the other file as well.... you can remove the data that it has but don't make anyother changes...

let me compare it manually...

[Dynamo Nath]

Won't be able to upload it till Monday if that's ok?

[Siddharth Rout]

yeah sure no probs... at least one problem is solved

[Dynamo Nath]

Here you go. If you put the protection on for worksheet and workbook hopefully you'll see what I mean. You can arrow across as well as up and down. In addition, if you arrow as far as you can go on one line it will move the cursor to the next line in that direction. None of this happens on the first example I sent you.

[Siddharth Rout]

I just tried it an it works just fine... Refer to post 17. ensure that you select the 1st option....

[Dynamo Nath]

I know the last one works ok. What I can't work out is why the original one won't work the same way unless I change the protection settings.

[Siddharth Rout]

Because the protection settings are preventing it from behaving in the desired manner...

[Dynamo Nath]

I don't think I'm explaining myself properly. The only way I can get both spreadsheets to work the way I want is to alter the settings on one of them; I should be able to use the same settings for both. It's not the end of the world, just annoying that I can't figure out why :-)

[Siddharth Rout]

Probably I am not able to understand what you want..

lets start from the basics...

From what I understand, you don't want to change the settings but you want the files to behave similar to each other? Am I correct?

[Dynamo Nath]

Yes, that's right. I shouldn't have to change the settings as essentially they are the same file with the possible exception that one has more records in it.

[Siddharth Rout]

But that is not possible as you are issuing different instructions to excel... Excel will do only what you tell it to do...

[Dynamo Nath]

I know it shouldn't be possible but that's what is happening, hence why I'm so confused by it :-)

Just to humour me, try setting the first spreadsheet settings to the same as the second one and see what happens.