Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
#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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
#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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
strange error calculations provides errors until i add any new one | Excel Worksheet Functions | |||
Excel Form File Errors | Excel Discussion (Misc queries) | |||
Shared sheet errors | Excel Discussion (Misc queries) | |||
Subtotal excl errors | Excel Worksheet Functions | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |