VBA to stop Ctrl PageUp/Down

[Lynden]

I have spent a great deal of effort stopping users from snooping into areas of my project (most of the ideas have come from this list - thanks) only to find out this week that Ctrl Page Up or Down allows them to move between my various sheets, even though I have removed the tabs!

Is there a quick way to disable the keystrokes Ctrl Page Up or Down?

Thanks to all of the regular experts who contribute to the knowledge of amateurs like me!

Kind regards, Lynden.

 

[Tom Urtis]

Yes that can be done, but first, have you tried to simply Hide or VeryHide them, and Unhide or UnVeryHide them, as you need to?

 

[yankee428]

Application.OnKey "^{PGUP}", ""

will take away pageup, but better yet, you can make the sheet veryhidden by going into VBE and changing the Visible property of the Sheet.

Of course for an experienced user, this is still easy to get around. if the data is more sensitive and you must completely lock others out of it read the instructions here:

http://www.mrexcel.com/board2/viewtopic.php?t=122487

 

[Lynden]

Hello Tom,

Thanks for the reply ... no I haven't tried this. Will give it a go, but would still be interested in how to stop this key stroke so that it could be switched off on Workbook_Open().

Kind regards, Lynden.

 

[Tom Urtis]

Place this in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.



Private Sub Workbook_Open()
Application.OnKey "^{PGUP}", ""
Application.OnKey "^{PGDN}", ""
End Sub

Private Sub Workbook_Activate()
Application.OnKey "^{PGUP}", ""
Application.OnKey "^{PGDN}", ""
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.OnKey "^{PGUP}", ""
Application.OnKey "^{PGDN}", ""
End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^{PGUP}"
Application.OnKey "^{PGDN}"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^{PGUP}"
Application.OnKey "^{PGDN}"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.OnKey "^{PGUP}"
Application.OnKey "^{PGDN}"
End Sub

 

[Lynden]

Thanks Tom!

Worked perfectly. Happy New Year!

Kind regards, Lynden.