Ctrl & PgUp/PgDwn not functioning on locked sheet?

[thumbtwiddler]

Hoping someone can help me here as I'm a bit confused and I'm sure I'm missing something simple.

Essentially, on a multi-tab file, my code protects and unprotects the sheet quite a lot but when it's protected, the Ctrl & PgUp or PgDown stop functioning correctly, as in doesn't move tabs but instead shoots you off to the right into column AE and keeps jumping each time but never moves tab.

However, if I step through ActiveSheet.Next.Select in debugger when the security is on, it moves as I'd expect. No change events or such like so I'm a bit confused.

Seems strange to me but I'm sure someone's come across it before (although I tried looking for it with search on this forum and failed, my search-fu is weak) - if so, any advice on why or what it's doing would be greatly appreciated.

Thanks in advance.

 

[Loomah]

hi
can you post your code with an explanation of what you're trying to acheive? i'm lost as to why you would want/need to use a piece of code like ActiveSheet.Next.Select as most things can be done without activating/selecting sheets.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[thumbtwiddler]

Might be making this sound more complicated than it is - there's no single piece of code I'm looking at, it's the functionality of the Ctrl PgUp & Dn that's suddenly gone to pot on this file when the sheet is locked via protection (as in Tools/Protection/Lock Worksheet - locked cells unselectable and uninputable). Using these hotkeys doesn't work any more, the activecell remains the same but the display on the screen shoots across to the right of the current sheet rather than moving through the tabs.

I'm not using the code for selecting the next sheet (ActiveSheet.Next.Select), I simply recorded a Ctrl PgUp and ran it through the debugger to see if there was a problem selecting the next sheet but the code ran fine but the hotkey didn't.

So basically, somehow I've managed to destroy the functionality of Ctrl PgUp/Dn somehow and can't figure out how - I've got data change events but no change events on the sheet that affect tab movement/cell selection. As stated above, the hotkeys works fine with security off but when security is turned on I get this weird situation.

Can't post the code - there's quite a lot of it and makes no sense without the file which is far too big and contains too much sensitive information sadly.

 

[SkipVought]

But you are using code to LOCK and PROTECT.

What is THAT code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thumbtwiddler]

Nothing more complex than the following -

Public Sub PWOff()
ActiveSheet.Unprotect Password:="xxxx"
End Sub



Public Sub PWOn()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="xxxx"
End Sub

TIA

 

[Loomah]

ok i'm afraid i don't know the answer - might be worth searching m$ site.

it appears that the ctrl+pgup/pgdn takes on the same funtionality as alt+pgup/pgdn if the sheet is protected with selection of locked cells denied. allow selection of locked cells and it works fine.

if you can live with it allow your user(s) to select locked cells as there doesn't seem to be any respite from this 'feature' even when there are unlocked cells to select.

sorry i have no more on this but i'd be interested to see if there is an explanation out there.....

happy friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[thumbtwiddler]

Answered my own question - have looked in Excel 07 and this "feature" still exists in that version (although on the bright side it's so much more difficult to find it lessens the chance of anyone using security in the first place - I mean come on, Review???).

Got round it via OnKeys intercept on the opening to change the functionality although has to do a check as to what tab you're on to make sure you don't fall off the end of the file when you keep PgDn'ing.

Thanks anyway.