Disabling the DELETE key

[tweitzel79]

Team,

I am creating a very large workbook within which I have created some validations and sheet / cell protection. I have some Yes / No drop downs that I want users to be able to change, so I cannot lock those cells. Validation provides protection only against changing, or removing information, but not the DELETE key.

Is there a way to disable the DELETE key throughout the entire Workbook? I have attempted some VB scripting, but I am a complete novice at VB. I would appreciate some help with EXACTLY what I need to put, and where.

I would want to disable the DELETE key for the entire workbook.

Thank you all for your time.

[Ron Coderre]

Perhaps you don't actually need to disable the DELETE key...Try this example:


With
B3: (containing the Data Validation cell)

• Set Cell B3 to UN-locked
• Set Cell C3 to LOCKED
• Select cells B3:C3
• Home.Format.Format_Cells…Alignment_tab
• Check: Merge cells
Note: I generally avoid merged cells, but in this case they are helpful.

Last….Protect the worksheet.

The end result:
Users will be able to select items from the dropdown list
but will not be able to delete the contents or paste
data into the cells.

Note: There are ways around that meager security, but
at least the users won't inadvertently ruin your sheet.

[tweitzel79]

I appreciate the input, however that doesn't seem to solve the problem. All that seems to do is extend the drop-down cell. Even with protecting it, the DELETE key still removes all options. Shucks.

[rcharters]

This should do the trick.

Please Login or Register  to view this content.

Reference: http://support.microsoft.com/kb/101567

RC

[JosephP]

have you tried

Please Login or Register  to view this content.

[tweitzel79]

I have tried various VB scripts but they don't seem to take effect. Where exactly should I be putting the scripts listed above?

[JosephP]

it depends when you want the key disabled. if it's for one sheet try the worksheet_Activate and worksheet_deactivate events in the worksheet code module to disable and enable it; if it's for the whole workbook use the workbook_open and workbook_beforeclose events in the thisworkbook module

[tweitzel79]

Forgive me for being a novice, but I am trying to accomplish this for the entire worksheet. The code I have entered in the "thisworkbook" module is:

Private Sub Workbook_Open()
Sub DisableKey()
Application.OnKey "{DELETE}", ""
End Sub

I get errors every time I try to open the workbook. I'd appreciate some clarification as to what I'm doing wrong.

[JosephP]

remove the

Please Login or Register  to view this content.

line

[tweitzel79]

HOORAY! You fine folks have helped solve a problem in 20 minutes that I had been beating myself up over for the past two days. Thank you very much.

[rcharters]

Do not forget to reset the delete key. You will have unhappy users if you don't.

Please Login or Register  to view this content.

RC