Disabling Ctrl, ALT keys

  • Thread starter Thread starter turk5555
  • Start date Start date
T

turk5555

I have an application that disables the CTRL and ALT key when a workbook
is open. The purpose is to prevent users from activitating a shortcut
key or hot key. I want to make sure that when I close the workbook
that the CTRL and ALT key work normally when I open a different
workbook or spreadsheet.
Would appreciate any help with this.

Thanks in advance.


Tommy
 
I assume that you talking about Excel.
How are the keys disabled? The normal way would be to use "OnKey", but CRTL
and ALT can only be specified in combination with other keys.

Let's assume that you have disabled the CTRL + TAB combination using

Sub TestDisableOnKey()
Application.OnKey "^{TAB}", ""
End Sub

You can enable it using

Sub TestEnableOnKey()
Application.OnKey "^{TAB}"
End Sub

More specifically, to ensure it is re-enabled when you close the workbook
add this procedure to "ThisWorkbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
TestEnableOnKey
End Sub

You also need to consider what happens when a user moves to and from that
workbook to another. These procedures (again placed in "ThisWorkboo") will
only allow the keys to be disabled when your main workbook is active.

Private Sub Workbook_Activate()
TestDisableOnKey
End Sub

Private Sub Workbook_Deactivate()
TestEnableOnKey
End Sub

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
This is the code that I added in ThisWorkbook:

Private Sub Workbook_Open()
Application.OnKey "%{F11}", ""
Application.OnKey "%{F8}", ""
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)

TestEnableOnKey

End Sub

This code disables the shortcut key ALT+F8 which displays the MACROS
list box and the shortcut key ALT+F11 which displays the Visual Basic
Editor. (NOTE: Your original code for enabling the shortcut keys upon
closing the workbook was not correct. I found the above version that
works)

When I open the workbook (workbook 1) that contains the above code this
works fine. If I open another workbook (workbook 2), and try to use
the above shortcut keys these shortcut keys remain disabled.

If I close the workbook 1, these shortcut keys remain disabled and I
can't use them in workbook 2. In order to enable these shortcut keys
it is necessary to close workbook 2 and closedown (exit) Excel, then
open workbook 1 and closedown Excel again.
When I open workbook 2 the shortcut keys are enabled.

Weird, but at least I understand what happens.

Thanks for all your help.



Tommy
 
See inline comments

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"


This code disables the shortcut key ALT+F8 which displays the MACROS
list box and the shortcut key ALT+F11 which displays the Visual Basic
Editor. (NOTE: Your original code for enabling the shortcut keys upon
closing the workbook was not correct. I found the above version that
works)
Click to expand...

The code was tested in Excel XP before posting - it works in XP - which
version are you using?
When I open the workbook (workbook 1) that contains the above code this
works fine. If I open another workbook (workbook 2), and try to use
the above shortcut keys these shortcut keys remain disabled.

If I close the workbook 1, these shortcut keys remain disabled and I
can't use them in workbook 2. In order to enable these shortcut keys
it is necessary to close workbook 2 and closedown (exit) Excel, then
open workbook 1 and closedown Excel again.
When I open workbook 2 the shortcut keys are enabled.
Click to expand...

Here are the notes from my first answer together with the Activate and
DeActivate procedures. Did you use them?

.... You also need to consider what happens when a user moves to and from
that
workbook to another. These procedures (again placed in "ThisWorkboo") will
only allow the keys to be disabled when your main workbook is active.

Private Sub Workbook_Activate()
TestDisableOnKey
End Sub

Private Sub Workbook_Deactivate()
TestEnableOnKey
End Sub
 
Back
Top