Disable Delete and Back Space key for a perticular Sheet but not for other sheets of same workbook

[PritishS]

Dear Sir/Madam,

I need a help to disable delete key and backspace key for a particular worksheet.

In details:

I'm using MS office 2007. I have a MS excel workbook with 88 sheets. All sheets are password protected except 88th (last one) worksheet. I want to disable delete and backspace operation from all 87 sheet but not for 88th sheet. I'm just learning Excel VB from internet and found some solutions. code is as below--

I used this code in 'Sheet1' (I thought using this code here (in perticular sheet) instead on ThisWorkBook would solve my problem, But it didn't)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey Key:="{DEL}", Procedure:="AlertUser"
End Sub

Then inserted a new module and used below code to show message

Public Sub AlertUser()
MsgBox "you push the delete button"
End Sub

Its gives me desired result. But I don't want to disable the 'delete' 'backspace' function to last sheet(88th).

Is it possible to apply this code for a particular 'Sheet' instead of whole workbook??

Please help me on this...Thanks in Advance

Thanks
PritishS

 

[dmt32]

Hi,
see if this solution helps you:

Place following code in STANDARD module:

Sub SetOnkey(ByVal state As Integer)
    '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"
End Sub

Place following code in the THISWORKBOOK code page:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  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




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
End Sub

Code should disable both Delete & BackSpace keys for all but last sheet in your workbook.
Code will also reset keys to normal operation when you switch to another window or close workbook.

Hope Helpful

Dave

 

[PritishS]

Hi Dave,

Thank you very much for your quick response. I'm going to try it now and let you know the result

Thanks

Pritish S

 

[PritishS]

Hi Dave,

I have done as instructed. But while compiling its is giving me an error in 'ThisWorkBook' saying-"Ambiguous Name detected: Workbook_SheetDeactivate and Workbook_WindowDeactivate. Any idea why I'm getting this error or am I doing anything wrong.

I have first pasted that 2nd code in 'ThisWorkBook' Page and then I have inserted a module and pasted the 1st code
Kindly look in to this and suggest me.

Thanks
PritishS

 

[dmt32]

In the Thisworkbook code page you most likely have those procedures & pasting my code duplicates them - you need to remove duplicate procedures.

Dave

 

[PritishS]

Hi Dave,

Thanks once again for getting back. Your code works fine for me and you are right. I already have used the same procedure in ThisWorkBook. But I used it for 'Back to last used sheet navigation. I want to keep that too. Here is the code

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lastsheet = Sh.Name
End Sub

following a Module code

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

And assigned it to a button.

Now after commenting this code in ThisWorkBook, your code works fine. can you please guide me how to use both of them so they should work their respective works?

Thanks
PritishS

 

[dmt32]

Should just be able to add it to the event.

 Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    If Sh.Index <> Worksheets(Worksheets.Count).Index Then SetOnkey xlOff
    
    lastsheet = Sh.Name
    
End Sub

Dave

 

[PritishS]

Hi Dave,

THANK YOU!! THANK YOU!!
It's working fine!! Got the result as expected. Thanks for your kind help
Have a good day!

Thanks
Pritish S

 

[hajiali]

how use this to specify multiple sheets instead of all but the last sheet or All sheet just Specify which sheets i want to Disable the Delete and Backspace key

Hi,
see if this solution helps you:

Place following code in STANDARD module:

Sub SetOnkey(ByVal state As Integer)
    '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"
End Sub

Place following code in the THISWORKBOOK code page:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  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




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
End Sub

Code should disable both Delete & BackSpace keys for all but last sheet in your workbook.
Code will also reset keys to normal operation when you switch to another window or close workbook.

Hope Helpful

Dave

 

[SKG2019]

All,

How do we disable the delete and backspace key for all worksheets.

Thanks
SKG