Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 7th 06, 09:39 PM posted to microsoft.public.excel.newusers
Field8585
 
Posts: n/a
Default #Value! errors


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?


--
Field8585
------------------------------------------------------------------------
Field8585's Profile: http://www.excelforum.com/member.php...o&userid=31282
View this thread: http://www.excelforum.com/showthread...hreadid=509519


  #2   Report Post  
Old February 7th 06, 10:25 PM posted to microsoft.public.excel.newusers
Dave Peterson
 
Posts: n/a
Default #Value! errors

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.



Field8585 wrote:

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?

--
Field8585
------------------------------------------------------------------------
Field8585's Profile: http://www.excelforum.com/member.php...o&userid=31282
View this thread: http://www.excelforum.com/showthread...hreadid=509519


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
strange error calculations provides errors until i add any new one Subzizo Excel Worksheet Functions 1 November 9th 05 12:39 AM
Excel Form File Errors hartleydc Excel Discussion (Misc queries) 0 September 29th 05 11:20 PM
Shared sheet errors Kevin M Excel Discussion (Misc queries) 0 September 26th 05 05:11 PM
Subtotal excl errors Rob Excel Worksheet Functions 2 May 2nd 05 02:49 PM
Unresolved Errors in IF Statements - Errors do not show in results Markthepain Excel Worksheet Functions 2 December 3rd 04 09:49 AM


All times are GMT +1. The time now is 02:25 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017