Disable Backspace and Delete key on specific Sheets

[hajiali]

I thought this was going to be easy as

[COLOR=#3366CC]Private Sub Workbook_Activate()[/COLOR]    Application.OnKey "{BACKSPACE}"
    Application.OnKey "{DELETE}"

End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "{BACKSPACE}"
    Application.OnKey "{DELETE}"[COLOR=#3366CC]End Sub[/COLOR]

Apparently its not working when adding this code its disables on all sheets in the workbook. I would like to be able to just disable Backspace and delete on specific sheet and not all sheets.

 

[lrobbo314]

This seems to be working. Put the code below in the sheet code for the sheet that you want the keys to be deactivated.

Private Sub Worksheet_Activate()
Application.OnKey "{BACKSPACE}", ""
Application.OnKey "{DELETE}", ""
End Sub


Private Sub Worksheet_Deactivate()
Application.OnKey "{BACKSPACE}"
Application.OnKey "{DELETE}"
End Sub

 

[Rick Rothstein]

I would like to be able to just disable Backspace and delete on specific sheet and not all sheets.

Are you sure you want to do this? I do not know your specific needs here, but I wonder what a user is to do if they accidentally hit the wrong key for one of their characters.

 

[hajiali]

I needing to do this as users are only able to double click to add value and double click to delete value in order. I want to prevent users to delete value out of order by using backspace or delete.

So this above code doesn't require anything to be activate? it Should be activate it as soon as the the excel opens? Also, instead of copy to 120 sheet that i want to disable delete and backspace all of them start with "CSA" and a number so I have sheet name "CSA1" "CSA2" "CSA3" etx. is there a way to rewrite the code to disable on all sheets that start with "CSA"?

 

[My Aswer Is This]

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
In upper left corner of window double click on This Workbook

Paste the code in the VBA edit window

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Modified  3/23/2019  11:33:35 PM  EDT
Dim ans As String
ans = Left(ActiveSheet.Name, 3)
If ans = "CSA" Then
Application.OnKey "{BACKSPACE}", ""
Application.OnKey "{DELETE}", ""
End If
End Sub

 

[hajiali]

Thank you so much "My Answer Is This" that works Wonderful. I REALLY REALLY APPRECIATE IT

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you so much "My Answer Is This" that works Wonderful. I REALLY REALLY APPRECIATE IT

 

[hajiali]

Ok so the code some reasons disable Backspace and Delete on all sheets including sheets that do now "CSA" in the Name at all. any reason why?

 

[My Aswer Is This]

Maybe we need this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Modified  3/24/2019  9:47:35 PM  EDT
Dim ans As String
ans = Left(ActiveSheet.Name, 3)
If ans = "CSA" Then
Application.OnKey "{BACKSPACE}", ""
Application.OnKey "{DELETE}", ""
End If
End Sub
Private Sub Workbook_Deactivate()
    Application.OnKey "{BACKSPACE}"
    Application.OnKey "{DELETE}"
End Sub

 

[hajiali]

Still same results. Just to make sure that this code is in the "THIS WORKBOOK"

I also have the following code in there

Private Sub Workbook_BeforeClose(Cancel As Boolean)Call Module1.hide_worksheet(True)
Call Module121.hide_worksheet(True)
ThisWorkbook.Close SaveChanges:=True
Application.CommandBars("Ply").Enabled = True
End Sub

Were module1 and Module121 Very hides certain sheets. not sure if that has anything to do with it

Maybe we need this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Modified  3/24/2019  9:47:35 PM  EDT
Dim ans As String
ans = Left(ActiveSheet.Name, 3)
If ans = "CSA" Then
Application.OnKey "{BACKSPACE}", ""
Application.OnKey "{DELETE}", ""
End If
End Sub
Private Sub Workbook_Deactivate()
    Application.OnKey "{BACKSPACE}"
    Application.OnKey "{DELETE}"
End Sub