#Value! errors

[Field8585]

I have experienced a problem that I am unable to find a rectification
for. When my staff constantly use the space bar to delete an entered in
error number and leave the cell blank (although there is a space in the
cell) any cell which uses that cell within its formulation shows
#Value! or #####. I am aware that they should not be using the space
bar to delete numbers and the space within the cell is the reason for
the other formulation errors. I guess my question is how do I format
the cell so that only a number and nothing else can be entered?

 

[Dave Peterson]

Maybe you can use Data|Validation to stop most problems.

But data|validation can be broken by copy|pasting.

You may want to consider modifying your formulas (yech!) to something like:

=N(A1)+N(A5)+N(A7)

(or use =sum(). It'll ignore text.)

Or use a worksheet event that looks for this kinds of changes.

If you want to try...

rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
On Error Resume Next 'just fly by any errors
For Each myCell In Target.Cells
If myCell.HasFormula = False Then
If IsEmpty(myCell.Value) = False Then
If Trim(myCell.Value) = "" Then
Application.EnableEvents = False
myCell.ClearContents
Application.EnableEvents = True
End If
End If
End If
Next myCell
On Error Goto 0
End Sub

One of the things that I don't like about this kind of event macro is that it
kills the Edit|Undo stack. If you can live with that problem, then maybe it's
worth a shot.