Leftfield question re worksheet security

[garry cook]

Well, I've tried various Excel VBA forums to no avail and figured what the hell, I'll ask round here incase anyone's come across this before and found a solution seeing as TM1 is so Excel heavy.

Essentially, on a worksheet which is security locked (as in normal Tools/Protection), if the "allow users to select locked cells" is unticked when setting (ie, users can only select unlocked cells), suddenly Ctrl & PgUp/PgDown stops functioning correctly and suddenly works in exact same manner as Alt & PgUp/PgDn - ie, rather than moving tabs in the workbook, it shoots off to the right/left of the current worksheet. Works fine if you allow locked cells to be selected (but obviously not changeable).

Anyone come across this before and have a way round it? Driving me nuts on a multitab file not being able to move tabs with Ctrl PgUp/Dn when the security gets set.

TIA

[Alan Kirk]

Well, I've tried various Excel VBA forums to no avail and figured what the hell, I'll ask round here incase anyone's come across this before and found a solution seeing as TM1 is so Excel heavy.

Essentially, on a worksheet which is security locked (as in normal Tools/Protection), if the "allow users to select locked cells" is unticked when setting (ie, users can only select unlocked cells), suddenly Ctrl & PgUp/PgDown stops functioning correctly and suddenly works in exact same manner as Alt & PgUp/PgDn - ie, rather than moving tabs in the workbook, it shoots off to the right/left of the current worksheet. Works fine if you allow locked cells to be selected (but obviously not changeable).

Anyone come across this before and have a way round it? Driving me nuts on a multitab file not being able to move tabs with Ctrl PgUp/Dn when the security gets set.

Sorry Garry, it's a known bug that's persisted over many versions. I know that Ron De Bruin (one of the survivors in the Excel newsgroups that I used to inhabit back when I had the time, and which I still lurk in from time to time) has reported it as a bug on a few occasions. However obviously MS feels that designing new user interfaces which destroy years of accumulated knowledge and skills takes priority over fixing broken functionality.

(And yes, the bug DOES still exist in Excel 2007. The big advantage of using 2007 though is that it takes you so naffing long to find where you protect the sheet (it's on the Review tab, by the way... I mean, "Review", naturally, where else would it be?) that you give up in frustration and simply don't bother protecting the sheet. See? That's MS looking out for you.)

Your only option is to have both select locked cells and select unlocked cells on, or both off. Or to just live with the difference in functionality, annoying as it is.

[paulsimon]

Hi Garry

I can't say that I ever use that keyboard shortcut. However, one possible work around might be to use OnKeys to intercept that key stroke and then write some VB to make it do what you want it to do.

Regards

Paul Simon

[garry cook]

Thanks chaps, OnKeys intercept works perfectly well (except for having to check what tab you're in before doing .previous/.next.select other wise get error when you try to tab off the end of the file).

Btw Alan, 873 posts already??? I thought that conquering Rome / Medieval Europe / building an empire (with boats!) would have stopped you getting that high a post count even if your job didn't

[Alan Kirk]

Thanks chaps, OnKeys intercept works perfectly well

Yes, good call by Paul there. I should have thought of that; it probably didn't occur to me since I've never used the "one on / one off" combination when locking.

(except for having to check what tab you're in before doing .previous/.next.select other wise get error when you try to tab off the end of the file).

Or just check that the sheet that you're going to is in bounds.

Code: Select all

Sub SwitchSheetsDown()

If ActiveSheet.Index < ThisWorkbook.Sheets.Count Then
    ThisWorkbook.Sheets(ActiveSheet.Index + 1).Activate
End If

End Sub

Sub SwitchSheetsUp()

If ActiveSheet.Index > 1 Then
    ThisWorkbook.Sheets(ActiveSheet.Index - 1).Activate
End If

End Sub

(NB: The code doesn't generate an error if the first or last sheet is hidden (it just ignores the command), and curiously it will skip over any sheets that may be hidden in the body of the workbook. Or at least, this is so in Excel 2003.)

Btw Alan, 873 posts already??? I thought that conquering Rome / Medieval Europe / building an empire (with boats!) would have stopped you getting that high a post count even if your job didn't

I have learned this from my quest for global domination; the ability to multitask is a necessity. (Well, semi-global domination, my old buddy Vlad Putin wants a chunk as well. And we think so much alike that I just couldn't say no...)

But sadly old chap, with a mere 15 posts in our year and a bit of existence you really are letting the "Legends of The Old Applix Forum" side down quite badly. You'll never make the OLAP Allstars series of "Survivor" at this rate...

[garry cook]

But sadly old chap, with a mere 15 posts in our year and a bit of existence you really are letting the "Legends of The Old Applix Forum" side down quite badly. You'll never make the OLAP Allstars series of "Survivor" at this rate...

Ach, just cause I don't post doesn't mean I don't keep my eye on it. Too bloody busy these days to reply much and besides, you chaps are doing such a good job of helping folk out that I'm always too late to reply to much anyway.

Thanks for the code Cap'n Kirk, works a treat

[Steve Vincent]

So busy in fact, he's even working Saturdays

Did find that same issue on one of my files but its not used enough for me to have tried finding a way around it. Would be useful to post the whole code in the useful tips section, i'm sure others will find it handy

[John Hobson]

Ach, just cause I don't post doesn't mean I don't keep my eye on it

While you're on can you have a word with the guys who sit at the front of your Eurofighter thingies and ask them to turn down the afterburners whilst they are flying over my house. I swear they make our foundations shake.

[Steve Vincent]

imagine the face of your cat when you live right next to the runway then

[John Hobson]

My cat purses his lips when he hears the noise from the Eurofighter so it can be hard tell what direction he's facing in