Disabling help functions with macro

[Christina]

Hi,

Doing some research. Want students to run through a "tutorial" and then
practice the Excel 2007 skills they learn in this tutorial. When using
Excel, DO NOT want them able to access Help via F1, the help button or the
help that comes up when you screw up a formula/function. Have a macro for
disabling F1....how to do the others??

 

[john]

Christina,
I don’t use 2007 so cannot answer all your questions.
However, following procedure should disable most of the keys on keyboard you
need to isolate but adjust code to suit your need.

The second procedure should restore the keyboard to normal use but be aware;
any assigned keys used as personal shortcuts may be lost.

As always, these things only work with macro’s enabled.

Use with care but hope helpful.

'
'*** Disable Function Keys,
'*** CTRL + most other Keys ...
'*** NB use with care!
Sub DisableKeys()
With Application
'Function Keys

' By themselves ...
For i% = 1 To 12
.OnKey "{f" & i% & "}", "do_nothing"
Next i%

' with Ctrl ...
For i% = 1 To 12
.OnKey "^{f" & i% & "}", "do_nothing"
Next i%

' with Alt ...
For i% = 1 To 12
.OnKey "%{f" & i% & "}", "do_nothing"
Next i%

' and with Shift.
For i% = 1 To 12
.OnKey "+{f" & i% & "}", "do_nothing"
Next i%

' Disable CTRL + most other keys
' NB Caps Lock does not have any
' effect on the action of the keys.

On Error Resume Next
' Some characters seem to cause
' an error when used with OnKey.

For i% = 32 To 122

.OnKey "^" & Chr(i%), "do_nothing"

Next i%

On Error GoTo 0

.OnKey "{ESC}", "do_nothing"

.OnKey "{HELP}", "do_nothing"


' .OnKey "%{TAB}", "do_nothing" 'ALT + TAB
End With

End Sub

Sub do_nothing()
Beep
MsgBox "Function Disabled!"
End Sub

'
'*** Restore normal
'*** keyboard operation.

Sub RestoreKeys()
With Application
'Function Keys
' By themselves ...
For i% = 1 To 12
.OnKey "{f" & i% & "}"
Next i%

' with Ctrl ...
For i% = 1 To 12
.OnKey "^{f" & i% & "}"
Next i%

' with Alt ...
For i% = 1 To 12
.OnKey "%{f" & i% & "}"
Next i%

' and with Shift.
For i% = 1 To 12
.OnKey "+{f" & i% & "}"
Next i%

' Re-enable CTRL + most other keys
On Error Resume Next

For i% = 32 To 122
.OnKey "^" & Chr(i%)
Next i%

On Error GoTo 0

.OnKey "{ESC}"

.OnKey "{HELP}"

' .OnKey "%{TAB}"
End With

End Sub

 

[Christina]

Sorry if I'm being dense...this looks like it will disable things like Ctrl +
1, etc. I just want to disable the help button (little question mark, upper
right corner) as well as the option to click Help that comes up in a dialog
box when you make a mistake in a function or formula. Thanks. _c

 

[john]

Hi Christina,
not dense at all - code i gave you knocks out most shortcut keys used to
access desktop - I just assumed that you would understand how to modify to
suit your own needs - sorry about that.

As i said in first post, I do not use 2007 so am unable to assist with some
of the other features you want to disable. Hopefully, this cut down version
of code is a start though. If you need values of other keys you want to
disable, goto vba help & search OnKey.

Hope of some use to you

Sub DisableKeys()

With Application

.OnKey "{F1}", "do_nothing"

.OnKey "{HELP}", "do_nothing"

End With

End Sub


Sub RestoreKeys()

With Application

.OnKey "{F1}"

.OnKey "{HELP}"

End With

End Sub

Sub do_nothing()
Beep
MsgBox "Function Disabled!"
End Sub

 

[Christina]

Gotcha! Thanks. _c

Hi Christina,
not dense at all - code i gave you knocks out most shortcut keys used to
access desktop - I just assumed that you would understand how to modify to
suit your own needs - sorry about that.

As i said in first post, I do not use 2007 so am unable to assist with some
of the other features you want to disable. Hopefully, this cut down version
of code is a start though. If you need values of other keys you want to
disable, goto vba help & search OnKey.

Hope of some use to you

Sub DisableKeys()

With Application

.OnKey "{F1}", "do_nothing"

.OnKey "{HELP}", "do_nothing"

End With

End Sub


Sub RestoreKeys()

With Application

.OnKey "{F1}"

.OnKey "{HELP}"

End With

End Sub

Sub do_nothing()
Beep
MsgBox "Function Disabled!"
End Sub