Modify existing code that disables the delete and backspace key

[danbates]

Hi,

Please can someone help me?

I have found the following code that disables the delete and backspace keys to all my sheets apart from the last sheet.

I was wondering if anyone knows how to modify it so it only works on sheet3 or better still limiting it to the range of D40:BM40 on sheet3?

Here is the code:

Place following code in STANDARD module:

[COLOR=#333333]Sub SetOnkey(ByVal state As Integer)[/COLOR]    'adapted from solution developed by
    'DMT32 aka Dave Timms & Jerry Sullivan MVP
    If state = xlOn Then
        With Application
            .OnKey "{DEL}", "'AlertUser ""Delete""'"             'Delete Key
            .OnKey "{BACKSPACE}", "'AlertUser ""BackSpace""'"    'BACKSPACE Key
        End With
    Else
        'reset keys
        With Application
            .OnKey "{DEL}"
            .OnKey "{BACKSPACE}"
        End With
    End If
End Sub




Public Sub AlertUser(ByVal Button As String)
    MsgBox "you pushed the " & Button & " button", 48, "Function Disabled"
[COLOR=#333333]End Sub[/COLOR]

Place following code in the THISWORKBOOK code page:

[COLOR=#333333]Private Sub Workbook_SheetActivate(ByVal Sh As Object)[/COLOR] 
If Sh.Index <> Worksheets(Worksheets.Count).Index Then SetOnkey xlOn
End Sub



Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Index <> Worksheets(Worksheets.Count).Index Then SetOnkey xlOff
End Sub


[COLOR=#333333]Place following code in the THISWORKBOOK code page[/COLOR]

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    If ActiveSheet.Index <> Worksheets(Worksheets.Count).Index Then SetOnkey xlOn
End Sub
 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    SetOnkey xlOff [COLOR=#333333]End Sub[/COLOR]

Any help would be much appreciated.

Thanks

Dan

 

[offthelip]

You don't need to change the onkey routine, but get rid of all the workbook event routines and then just put these two into worksheet 3

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("D40:BM40")) Is Nothing Then
   SetOnkey xlOn
 Else
    SetOnkey xlOff
 End If


End Sub


Private Sub Worksheet_Deactivate()
    SetOnkey xlOff
End Sub

 

[danbates]

Hi offthelip,

Thank you for your help and it's working perfectly.

Thanks
Dan