Excel 2007 Hotkey question. 1

[PureSlate]

Hi,

As part of a template I'm making, several "on event" macros I have running prompted me to keep one worksheet in the template locked without the ability to select the locked cells. For some reason, this has the side effect of disabling ctrl-pageup and ctrl-pagedown to switch between worksheets. While I could theoretically modify the on events to be more specific and allow locked cells to be selected, that would be a fairly arduous task.

Is there a way to keep these hotkeys active without allowing locked cells to be selected?

Thanks!

 

[SkipVought]

hi,

please explain how your sheet is designed and the code for your hotkey.

you can always 1) UNprotect, 2) currect code 3) protect to accomplish.

Best to post VBA questions in forum707.

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

 

[PureSlate]

Sorry, I am refering to the functionality of the built-in change worksheet up/down hotkeys. The code is designed as an on event cell selection code, and as such I want to limit which cells can be selected. However, when I enable the option, the hotkey (built-in) no longer functions as expected.

 

[SkipVought]

built-in change worksheet up/down hotkeys

Build-in? Comes from Excel out fo the box feature? Please explain as I am not familiar with this feature.

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

 

[PureSlate]

Ctrl-pageup and ctrl-pagedown selects the previous/next worksheet respectively, out of the box for Excel. (At least 2007, but I think 2010 and 2013 as well.)

 

[SkipVought]

Okay, I think I have the picture.

Seems that having a sheet that you cannot select any cell on, does a number on this hot key set.

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

 

[byundt]

You might try using Application.OnKeys to assign your own macros in place of CTRL + PageUp and CTRL + PageDown.

Put all of the following code in ThisWorkbook code pane:

Private Sub Workbook_Open()
AssignKeys
End Sub

Private Sub Workbook_Activate()
AssignKeys
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RestoreKeys
End Sub

Private Sub Workbook_Deactivate()   'Restore normal functioning of keys
RestoreKeys
End Sub

Private Sub AssignKeys()
Application.OnKey "^{PgUp}", "ThisWorkbook.SheetShifterF"
Application.OnKey "^{PgDn}", "ThisWorkbook.SheetShifterR"
End Sub

Private Sub RestoreKeys()
Application.OnKey "^{PgUp}"
Application.OnKey "^{PgDn}"
End Sub

Sub SheetShifterF()
Dim i As Long, n As Long
i = ActiveSheet.Index
n = ThisWorkbook.Worksheets.Count
i = (i + 1) Mod n
If i = 0 Then i = n
ThisWorkbook.Worksheets(i).Activate
End Sub

Sub SheetShifterR()
Dim i As Long, n As Long
i = ActiveSheet.Index
n = ThisWorkbook.Worksheets.Count
i = (i - 1) Mod n
If i = 0 Then i = n
ThisWorkbook.Worksheets(i).Activate
End Sub

Brad

 

[PureSlate]

Thanks Bryan!

This code worked almost perfectly! This is nitpicking, but I swapped the direction the page up and page down by switching the referenced subs on keypress to match the excel hotkeys.

Sorry for the delay, I had to wait until we were working on a new version of the template to implement this code. I'm not sure if you wanted it, but I threw a comment in there crediting you as well. Thanks again!