If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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
|
|||
|
|||
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Treeview ActiveX Mouse errors | dave k | Using Forms | 7 | November 26th, 2005 02:09 PM |
Looking for help with autoenrollment errors | Wayne Fischer | General Discussion | 1 | July 30th, 2005 07:33 AM |
Shared Tasks and early reminders | Adam Griffiths | General Discussion | 1 | June 16th, 2005 04:22 AM |
Trapping Errors Via Automation | Alex White MCDBA MCSE | General Discussion | 4 | May 13th, 2005 08:24 AM |
Too many spelling errors | Greg Reese | Formatting Long Documents | 4 | November 3rd, 2004 11:46 PM |