help on error checking (basic)

im a newbie to this excel programing..
currently..i use the function to sum up cells..
However i need to do error checking...if the sum of A1 and A2 is more
than 1..it will not allow  A2 to input the number. how to do that? 

thanks in advance!


-- 
forevercalz
------------------------------------------------------------------------
forevercalz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28931
View this thread: http://www.excelforum.com/showthread.php?threadid=486754

0
11/21/2005 5:43:21 AM
excel 39879 articles. 2 followers. Follow

3 Replies
447 Views

Similar Articles

[PageSpeed] 6

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2"

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Value + .Offset(0, -1).Value > 1 Then
                MsgBox "Sum invalid"
                .Value = ""
            End If
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"forevercalz" <forevercalz.1yu10z_1132551901.3453@excelforum-nospam.com>
wrote in message
news:forevercalz.1yu10z_1132551901.3453@excelforum-nospam.com...
>
> im a newbie to this excel programing..
> currently..i use the function to sum up cells..
> However i need to do error checking...if the sum of A1 and A2 is more
> than 1..it will not allow  A2 to input the number. how to do that?
>
> thanks in advance!
>
>
> -- 
> forevercalz
> ------------------------------------------------------------------------
> forevercalz's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28931
> View this thread: http://www.excelforum.com/showthread.php?threadid=486754
>


0
bob.phillips1 (6510)
11/21/2005 9:26:08 AM
Well thanks..however..your code only apply to validate one cell at 
time..as i have many columns...A1 A2 A3 A4 A5 to validate....so i hav
to paste  5 times the code each with different variable??? is there 
way to declare all the columns in one variable?



------------------------------------------------------------------------
> forevercalz's Profile:[/color]
http://www.excelforum.com/member.php?action=getinfo&userid=28931
> View this thread
http://www.excelforum.com/showthread.php?threadid=486754


--
forevercal
-----------------------------------------------------------------------
forevercalz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2893
View this thread: http://www.excelforum.com/showthread.php?threadid=48675

0
11/22/2005 3:25:18 AM
You originally said that if the sum of A1 and A2 is more than 1..it will not
allow A2 to input the number. If this is so, which cell will be checked with
A1, as there is no previous row.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"forevercalz" <forevercalz.1yvpfy_1132630200.6314@excelforum-nospam.com>
wrote in message
news:forevercalz.1yvpfy_1132630200.6314@excelforum-nospam.com...
>
> Well thanks..however..your code only apply to validate one cell at a
> time..as i have many columns...A1 A2 A3 A4 A5 to validate....so i have
> to paste  5 times the code each with different variable??? is there a
> way to declare all the columns in one variable?
>
>
> >
> ------------------------------------------------------------------------
> > forevercalz's Profile:[/color]
> http://www.excelforum.com/member.php?action=getinfo&userid=28931
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=486754
> >
>
>
> -- 
> forevercalz
> ------------------------------------------------------------------------
> forevercalz's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28931
> View this thread: http://www.excelforum.com/showthread.php?threadid=486754
>


0
bob.phillips1 (6510)
11/22/2005 8:44:54 AM
Reply:

Similar Artilces:

Input data check
Hi I want to check 10 fields to confirm they have data entered into them. Before I print a document. Field1 Not is null Field2 Not Isnull And so on. If any of the fields are blank I want a msgbox to appear listing the field names which have no data. I do not want to set the field to, data required, because there may be a reason why we have been unable to enter the required data. How do I set this up? Thanks Bob Bob, Something like... Use check boxes, combo boxes, text boxes, etc... If IsNull(Me!CheckBox1) Then Msg...

Help coping formulas
Excell 2007 I have an IF formula for contents in workbook A. I would like to copy the IF formula into workbook B and have it apply to the contents of workbook B. Suggestions please. Thank you, Bill Does not simply "copy, paste" work ? If I'm not missing something in your question, it should work. Cousin Excel "Bill" wrote: > Excell 2007 > > I have an IF formula for contents in workbook A. I would like to copy the IF > formula into workbook B and have it apply to the contents of workbook B. > > Suggestions please. &g...

Payables Select Check Discount Date
In the Payable Select Checks window, is there any way to select only those invoices that have a discount as of a particular Discount Date? We want it to select only the invoices with a discount available. I've been testing this but you have to enter a Due Date Cutoff in order to enter a Discount Date cutoff. That will then include all invoices as of that due date, regardless of whether or not there's a discount. ...

Help! Need to break down information
Hello All - I'm building a product information database at work and have come across a snag. Our product titles are simply a sentence of sorts, but in the new db they need to be broken up into three fields rather than one. Each field also has a character limitation. Is there a way I can take the product title from one sheet in the workbook and have it broken into three pieces (obeying the character limitations of each field) on another sheet? (Including spaces- the first field has a 20 character limitation, as does the second, and the third is only 10 characters.) Thanks! tankgirl...

MS Office Publisher Help window appears after every print job
I've just started using Publisher 2003 and am doing fine with most of it. However, one thing is driving me crazy... every time I send a job to a printer, whether it's an actual printer like my HP Laserjet 4MV or a virtual printer like the Acrobat PDF 7, after the job spools out, the MS Office Pint Help window appears, on a page that says "Troubleshoot printing publications on a desktop printer." I didn't clikc on help. I don't need help, because the jobs print fine... why does this window keep appearing, and more importantly, how do I make it stop appearing sh...

Check lead 's values in a Workflow for opportunity
I want to create a workflow on an opportunity but this workflow will update object (Opportunity fields) based on customised values on the corresponding Lead . But when I try to do that workflow manager only allow me to check values for the following objects only: .. Opp .. Account .. Contact .. Owner Any ideas ? Add identical fileds to the Opportunity and then modify the mapping between the Lead and the Opportunity to copy those fields. Then you can reference the values. Matt Parks ---------------------------------------- ---------------------------------------- On Wed, 7 Apr 2004 12:...

Reporting Services Permission Error
Recently I migrated our CRM Databases to a server running SQL 2005 from a box running SQL 2000. Our CRM Server is on a different box - and this box (CRM Server) is running reporting services for CRM. I updated the config file in Reporting Services to reflect the new server name. When running reports, I now get the following error: An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'DSNumandCurrency'. (rsErrorExecutingCommand) Get Online Help SELECT permission denied on object 'fn_GetFormatStrings',...

Money 2000
I have been using Money 2000 for some time and just recently started getting the following message whenever I try entering a transaction with a new payee: "Money could not write to your Money fiel. The operatoin cannot be performed or another application finished this task before you. Please try again." Any idea what is the cause and fix for this problem? In microsoft.public.money, Dominique Guillard wrote: >I have been using Money 2000 for some time and just >recently started getting the following message whenever I >try entering a transaction with a new payee: >...

Error 553 using Outlook 2007 and Vista
Hello. I'm getting the following error using Outlook 2007 and Vista: 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.5.3 - chkuser) When I test the account settings, it's fine. I can recieve just fine, and the only outgoing messages without this error are for recepients with my same domain. I've turned off my antivirus and windows firewall. Still this doesn't work. Any help would be greatly appreciated. 550 errors usually mean you're not authenticating properly, or you're attempting to use an SMTP server other than the one your ISP provides. ...

Help with X- Axis!!!
Hi. I am trying to graph data in Excel. My x-axis is days. I have data points for Day 1, Day 4 and Day 10. When I graph, it doesn't space the data points proportionally. There is the same distantance between 1 & 4 as there is between 4 & 10. Its like it gets treated as categorical versus numerical. Any guidance would be greatly appreciated. Also, is there a way that I can insert tick marks on the x-axis only where I have data points (only on days 1, 4 and 10 on the example above)? Thank you. Chris Are you using a line chart? It sounds like you need an XY chart inste...

Error Message in Outlook 2003 #5
Hi all, I Open a mail-item folder such as Inbox and click the New button on the Standard toolbar A blank Message form for creating a new outgoing email message appears. I Complete the Message form and send the message or so I think. I seem to get another dialog box that tells me I cant send as there is a message open already. Any suggestions, cheers, andy. ...

error: An open operation on table "SY_Pathnames" has caused an unknown error (161)
Hi Friends, I have a Windows 2000 Server and MS Great Plains Dynamics installed on Pervesive SQL Server running on the same hardware machine. The machine also had Exchange 2000 Server. The Exchange 2000 was moved to another machine and uninstalled. The problem: After the uninstallation of Exchange 2000, the Great plains users are getting the following error "An open operation on the table "SY_Pathnames" has caused an unknown error (161)". i.e. only one user can login to the Great Plains application at a time. Currently they have a license for 4 users, but only on...

Flexible default y-axis (Help)
Where do you see the ability to change the the XY plane to cross at the minimum value? I only have the options to change it to cross at the maximum value. I am using Access 2003. The only other option is to set it to cross at a value you enter in, but then I am back to entering it in. I tried the second solution " Set objAxis = objGraph.Axis(2) 'Y Axis" but this tells me this object is unavailble. Thanks for the help, would be huge for me to make work. Here is the original thread "SA" wrote: > Sue: > > Yes, it is possible to do this, there are ...

Terminal server install domain problem help please
Hi everyone, this is my setup windows 2003 sbs acting as domain controller, exchange, microsoft crm ect windows 2003 standard acting as a terminal server and member server I had a problem with folder redirection which works fine but i manually had to give permission to each individual on the sbs server to the folder redirection folder, is this normal? seems cumbersum My problem is i have install microsoft crm on the 2003 sbs and now i am trying to configure access via the outlook client on the terminal server but i can only get it to work for the administrator. i noticed under the term...

error on opening outlook XP
I had to reinstall and now when I open I contantly get the following Microsoft Outlook Can't load speech recognition files. Microsoft Outlook cannot install the necessary files due to Windows installer error 1606. Feature ID not registered. I have checked in Knowledge base and have not been able to find anything. Any help would be greatly appreciated. ...

"Memory" errors in Publisher 2002
A customer reports that Publisher 2002 was running fine. Now the program will open, but whenever you do anything (open a file, click Help/About, etc.) you get an error message: The instruction at "0x00000000" referenced memory at "0x00000000". The memory could not be "read." The hex numbers change. I can find nothing about this specific behavior in the MS knowledge base. I removed and reinstalled the product but to no avail. Any ideas? Thanks Mike Possibly corrupt printer driver. Try shorting the name of the printer to 8 characters. If that does no...

Help with Macro please!
Hi all, I am trying to get a macro to work in Excel. Basically, this is what I want it to do: A user will enter a date into a cell, say A1 on Sheet 1. They then enter some sales figures in cells below this. These figures are picked up on Sheet 2 in Row 1 so that they are all in a row. Below this row I have all the days of the year. Once the user has finished entering the figures, the macro needs to copy the row of data on Sheet 2 and paste Values Only to the row corresponding to the date entered. This way, I am building up a year of data. I don't want users entering data directly ...

Please Help!
I have posted this problem before but no one has responded. Two of the files I am trying to load have vital info. I have uninstalled MS Office and then reinstalled with no results. Everytime I try and load an xls file I get the message: "Class not registered. Looking for object with CLSID:{AC9F90-E877-11CE-9F68- 00AA00574A4F}" Excel will load but I can't get the file to open. Any help would be very much appreciated. Try this http://support.microsoft.com/default.aspx?scid=kb;en-us;182500&Product=xlw it seems you missed 2 characters in the string you posted, the KB...

Outlook 2002 error message #6
I get an error message that cites 0x80042108 sending and receiving error. What's the fix? Thank you! ...

DPM 2007 access denied errors
A couple days ago we had to reboot one of our servers (not nicely) and now DPM needs to run a consistency check with the server to correct/sync any changes, but it has been failing every time it tries. Here is the error details: Type: Consistency check Status: Failed Description: DPM encountered an error while performing an operation for \\?\Volume{2d49d72a-ae80-11dd-bf9d-806e6f6e6963}\Documents and Settings\Administrator.MEDIBASE\Cookies\administrator@at.atwola[2].txt (ID 2033 Details: Access is denied (0x80070005)) End time: 1/29/2010 9:41:56 AM Start t...

Please help #14
Hi, I am trying to find a formula that allows me to know how many days an employee took as a vactaion on each month a summary sheet for each employee In order to find the number of days taken i have use the following : B22=From (date) C22= To Date D22= Total Taken C22:C44 =Official Holidays =NETWORKDAYS(B22,C22,C44:C53) Please advise Tia I've seen this, and variations of it, posted in several newsgroups - it is not a good idea to multi-post. You give no details of where your employee names are located, so are we meant to guess how your data is laid out? Pete On Mar 26, 9:26=A0am...

Spell Check Meomory
On my old computer spell check had a memory so that when I went back to spell check a large document I had updated, I did not have to trawl thorugh dozens of words that had already been checked and ignored. How do I find this feature on Microsoft Office Word 2007? -- Ashton ...

Error: Cannot expand named range
I have created and linked an Access form to do data entry on my Excel spreadsheet. Once I get passed a few records I get the cannot expand named range error. In the spreadsheet I had up to 4 records, but when I click on the View MS Form Button, I will only see 2 records. What causes this and how do I fix it? what is defining your named range. -- Don Guillett SalesAid Software donaldb@281.com "Patty" <anonymous@discussions.microsoft.com> wrote in message news:0f2901c3a9f1$9c88a840$a101280a@phx.gbl... > I have created and linked an Access form to do data entry >...

Help Please #8
Thanks For The Info : -- mikeee ----------------------------------------------------------------------- mikeeee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=26424 ...

Type Mismatch error
Any idea why I am getting a "Data Type mismatch on criteria expression" error on the second line? MaxCAR = DMax("[CARNumber]", "tblCARs") LastAuditNumber = DLookup("[AuditNumber]", "tblCARs", "[CARNumber] = '" & MaxCAR & "'") Both Fields are dimensioned as Integers CARNumber is and Auto number field -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200911/1 Because you have delimited it with single quotes which represents a string. Wh...