Disable the Delete Key

C

CarolK1968

Is there a way to disable the use of the delete key or change it so that when
it is pressed, it displays a zero?

I am using the Essbase Add-in and if a blank field is submitted, it does not
replace the prior values in the field. I need to make sure that there are no
blank cells if there are, I need to replace them with zeros.

Any help would be appreciated.
 
K

Kevin B

This won't disable the delete key, but it will let you pre-populate the cells
with a 0.

Select the entire data range and press the F5 key. In the dialog box, click
the SPECIAL command button in the lower left-hand corner and then click the
BLANKS option button and click the OK command button to return to the
workbook.

Type a 0 (zero) and then press <Ctrl> + <Enter> to enter the value in all
selected cells.

Hope this helps...
 
C

CarolK1968

I already have the cells pre-populated with zeros and/or the values
previously entered. I have conditional formating to show if the cell is
blank.

Thank you for the suggestion.
 
G

Gord Dibben

This sheet event code will replace a deleted item with a 0

Can you use that?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
If .Value = "" Then
.Value = 0
End If
End With
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.


Gord Dibben MS Excel MVP
 
Top