Page 1 of 1
Leftfield question re worksheet security
Posted: Sat Jul 11, 2009 10:07 am
by 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
Re: Leftfield question re worksheet security
Posted: Sat Jul 11, 2009 10:32 am
by Alan Kirk
garry cook wrote: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.
Re: Leftfield question re worksheet security
Posted: Sat Jul 11, 2009 11:03 pm
by 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
Re: Leftfield question re worksheet security
Posted: Sun Jul 12, 2009 8:03 pm
by 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
Re: Leftfield question re worksheet security
Posted: Mon Jul 13, 2009 5:25 am
by Alan Kirk
garry cook wrote: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.
garry cook wrote:(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.)
garry cook wrote: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...
Re: Leftfield question re worksheet security
Posted: Mon Jul 13, 2009 7:43 am
by 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
Re: Leftfield question re worksheet security
Posted: Mon Jul 13, 2009 7:52 am
by 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
Re: Leftfield question re worksheet security
Posted: Mon Jul 13, 2009 8:39 am
by 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.
Re: Leftfield question re worksheet security
Posted: Mon Jul 13, 2009 4:34 pm
by Steve Vincent
imagine the face of your cat when you live right next to the runway then
Re: Leftfield question re worksheet security
Posted: Tue Jul 14, 2009 7:19 am
by 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