Disable Keys or Key Combinations

[MSingh]

Hi,

Please advise, the ffg. code by Ron De Bruin:

In my Workbook_Activate using the code below my normal alphabet & number keys intermittently do not function, switch between worksheets or delete data & they function normally. This could be perculiar to my worksheet setup or is there something i'm missing:

Disable almost every key or key combination

Sub Disable_Keys()
Dim StartKeyCombination As Variant
Dim KeysArray As Variant
Dim Key As Variant
Dim I As Long

On Error Resume Next

'Shift key = "+" (plus sign)
'Ctrl key = "^" (caret)
'Alt key = "%" (percent sign
'We fill the array with this keys and the key combinations
'Shift-Ctrl, Shift- Alt, Ctrl-Alt, Shift-Ctrl-Alt

For Each StartKeyCombination In Array("+", "^", "%", "+^", "+%", "^%", "+^%")

KeysArray = Array("{BS}", "{BREAK}", "{CAPSLOCK}", "{CLEAR}", "{DEL}", _
"{DOWN}", "{END}", "{ENTER}", "~", "{ESC}", "{HELP}", "{HOME}", _
"{INSERT}", "{LEFT}", "{NUMLOCK}", "{PGDN}", "{PGUP}", _
"{RETURN}", "{RIGHT}", "{SCROLLLOCK}", "{TAB}", "{UP}")

'Disable the StartKeyCombination key(s) with every key in the KeysArray
For Each Key In KeysArray
Application.OnKey StartKeyCombination & Key, ""
Next Key

'Disable the StartKeyCombination key(s) with every other key
For I = 0 To 255
Application.OnKey StartKeyCombination & Chr$(I), ""
Next I

'Disable the F1 - F15 keys in combination with the Shift, Ctrl or Alt key
For I = 1 To 15
Application.OnKey StartKeyCombination & "{F" & I & "}", ""
Next I

Next StartKeyCombination


'Disable the F1 - F15 keys
For I = 1 To 15
Application.OnKey "{F" & I & "}", ""
Next I

'Disable the PGDN and PGUP keys
Application.OnKey "{PGDN}", ""
Application.OnKey "{PGUP}", ""
End Sub

Thanks again
Mohamed

[HansV]

Why do you want to do this? If I were one of your users, I would become so frustrated that I would refuse to use the workbook...

[MSingh]

Hi Hans,

The workbook is an add-on to an accounting software. Data from the workbook is reformatted, exported into csv, then imprted into the accntg software. In so doing, the workbook eliminates an entire process in the accounting cycle & goes a step closer to real-time reporting rather than reporting trading results after say 3 weeks after month-end.

Apart from activex calendar (becoz date format is so critical) & listboxes, user enters data directly into worksheet cells. So, there are no further "data validation" rules that he would have complied with, if for example, he was using other userforms like textboxes to ensure correct data types.
The .xlsm assigns with application.onkey "mymacros" to ensure he still has excel-type maneauvarability. With keycombinations being disabled (& reassigned to other macros) cut-copy-paste of embedded formulas & cell data validation drop-down lists are maintained intact. The application that this .xlsm integrates with is also specific in its key functions, so he is in an "environment" that is familiar & has a similar "feel" to what he is used to. (He also has an F1 help).

Hope this explains of project.

Thanks
Mohamed

[HansV]

I still think it's far too restrictive, but never mind...

In the meantime, I've copied your code into a module in a workbook and run it, then played with the workbook from time to time.
It operates as intended, I haven't noticed the letter and number keys becoming disabled.
Apparently you have more code that enables/disables keys, perhaps the culprit is there?

[MSingh]

Hi Hans,

I will heed you observation- re: "restrictiveness", should the user find it inhibitive, i'll have to modify & perhaps include some userforms for data entry.

Yes, the main worksheet on which this code operates is the "engine" with lots of other code & worksheet change events, i agree the culprit might be there, i'll do some other testing on other pc's then revert to you.

Kindest regards
Mohamed