Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to get a warning when data is entered wrongly
i have a spreadsheet. The ENTRY and EXIT should tally.
ENTRY----total of col A:D is in E3. EXIT---- total of col G:J is in K3. i expect to get E3=k3 cell K3 is formatted as the sum of G:J when i enter a data wrongly in anywhere between the cells G:J a warning should come in col K3 (of course after entering data in all the four cells G:J).the idea is the number in entry should tally with that of the exit. is it possible? the excel file is attached. Last edited by sumesh56 : October 18th 12 at 05:39 PM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get a warning when data is entered wrongly
On Tuesday, October 16, 2012 10:30:23 AM UTC-7, sumesh56 wrote:
i have a spreadsheet. The ENTRY and EXIT should tally. ENTRY----total of col A:D is in E2. EXIT---- total of col G:J is in K2. Cell K is formatted as E2=K2 cell K2 is formatted as the sum of G:J when i enter a data wrongly in anywhere between the cells G:J a warning should come in col K2(of course after entering in all the four cells G:J).the idea is the number in entry should tally with that of the exit. is it possible? the excel file is attached. +-------------------------------------------------------------------+ |Filename: entry and exit.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=626| +-------------------------------------------------------------------+ -- sumesh56 See if this does what you want. Copy into the sheet module. Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Integer, J As Integer I = Range("E3") J = Range("K3") If I < J Then MsgBox "E Does Not Equal K" End If End Sub Regards, Howard |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
Quote:
Press Alt+F11 to open the Visual Basic Editor, and then click Insert, Module.i selected cell L and copied as you suggested. then i got a message from the excel. it says-the following features cannot be saved in macro free workbooks-vb project. to save a file with these features click No and then choose a macro enabled file type in the file type list. i have never used things like you suggested. plz help with directions. i am using excel 2007. Last edited by sumesh56 : October 17th 12 at 06:55 PM |
#5
|
|||
|
|||
Quote:
It depends upon what is the type of validation you want. For example if you are only looking for a message. You can write a simple formula that compares the two total cells and return an error message. But this wouldn't stop the user from making the mistake. If you want the user to not proceed until the two totals match you can use a VBA code and attach it to the Worksheet_Change event. Not sure if you can do this yourself. If you need my assistance for the VBA code, please let me know. Thank You, Prashant |
#6
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
Quote:
thanks for the reply. i did your steps. but sorry,it does not work as i wished. pls see the attached excel file again. I have to enter the data of "entry" from col A:D and its total is in col E3. I would like to make entries A:D without getting any error message. the total should come in E3 of course without any error message. then comes to the next part"exit" entries of which will be in col G:J. here also I should be allowed to make entries in col G:J without EM. Now comes the main part. the total of G:J should come in col K3. Here in col K3 if the total does not equal E3 because of the entries in col G:J are wrong, I should get an EM. Now after doing the VB code,i get EM after each and every data entry in any one of the col from A:D and G:J. I have to hit the Enter button twice to get rid of the EM and to pass the cursor to the next cell. E3=K3 if it does not equal i should get the EM.I require something which can connect only with these columns and not with other columns. i think i could express my idea.thanks Last edited by sumesh56 : October 18th 12 at 05:28 PM |
#9
|
|||
|
|||
Quote:
|
#10
|
|||
|
|||
somehow this post has a duplicate post. how can i delete the other one?
|
#11
|
|||
|
|||
Quote:
Attached is the updated file and hopefully contains everything you were looking for. It does the following. Wouldn't give Error Message if any of the cells (in Entry or Exit boxes) is left empty. Would give error if only all of the cells are filled in the two sections and their total don't match. Moreover, it undoes your last action to resume to original state. Let me know if this works for you. Thanks, Prahsant |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get a warning when data is entered wrongly
On Thursday, October 18, 2012 9:25:33 AM UTC-7, sumesh56 wrote:
;1606468 Wrote: On Wednesday, October 17, 2012 10:25:30 AM UTC-7, sumesh56 wrote:- ;1606419 Wrote: - On Tuesday, October 16, 2012 10:30:23 AM UTC-7, sumesh56 wrote:-- -- i have a spreadsheet. The ENTRY and EXIT should tally.-- -- -- -- ENTRY----total of col A:D is in E2.-- -- -- -- EXIT---- total of col G:J is in K2.-- -- -- -- Cell K is formatted as E2=K2-- -- -- -- cell K2 is formatted as the sum of G:J-- -- -- -- when i enter a data wrongly in anywhere between the cells G:J a-- - warning- -- -- -- should come in col K2(of course after entering in all the four cells-- -- -- -- G:J).the idea is the number in entry should tally with that of the-- - exit.- -- -- -- is it possible?-- -- -- -- the excel file is attached.-- -- -- -- -- -- -- -- -- -- -- -- +-------------------------------------------------------------------+-- -- -- -- |Filename: entry and exit.zip |-- -- -- -- |Download:-- - http://www.excelbanter.com/attachment.php?attachmentid=626|- -- -- -- +-------------------------------------------------------------------+-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- sumesh56--- - - - See if this does what you want. - - Copy into the sheet module.- - - - Private Sub Worksheet_Change(ByVal Target As Range)- - Dim I As Integer, J As Integer- - I = Range("E3")- - J = Range("K3")- - If I < J Then- - MsgBox "E Does Not Equal K"- - End If- - End Sub- - - - Regards,- - Howard- thanks for the reply. but ,i am not able to understand what do you mean by sheet module. where can i find that? +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- sumesh56- Copy the code I sent, Ctrl + c, right click the sheet tab and click on View Code. This will take you to the vb editor. Paste, Ctrl + v, the code in the large white area. Now hit Alt + F11 to return to the worksheet. Test fly the code by making entries in notable cells. If E3 and K3 do not match you should get a Message Box alerting you. Note, in your post you mention E2 & K2 not matching. I assumed you meant E3 & K3. Regards, Howard hai Howard, thanks for the reply. i did your steps. but sorry,it does not work as i wished. pls see the attached excel file again. I have to enter the data of "entry" from col A:D and its total is in col E3. I would like to make entries A:D without getting any error message. the total should come in E3 of course without any error message. then comes to the next part"exit" entries of which will be in col G:J. here also I should be allowed to make entries in col G:J without EM. Now comes the main part. the total of G:J should come in col K3. Here in col K3 if the total does not equal E3 because of the entries in col G:J are wrong, I should get an EM. Now after doing the VB code,i get EM after each and every data entry in any one of the col from A:D and G:J. I have to hit the Enter button twice to get rid of the EM and to pass the cursor to the next cell. E3=K3 if it does not equal i should get the EM.I require something which can connect only with these columns and not with other columns. i think i could express my idea.thanks +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- sumesh56 Try this, assumes you will make all the entries and the activecell is K3 after all those entries are made. It works for me to start in cell A3 and after each entry use the "Right Arrow" key to advance throught the field and after the entry in J3, you will be in cell K3. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell < Range("K3") Then Exit Sub Dim I As Integer, J As Integer I = Range("E3") J = Range("K3") If I < J Then MsgBox "E Does Not Equal K" End If End Sub Regards, Howard |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get a warning when data is entered wrongly
On Thursday, October 18, 2012 1:44:49 PM UTC-7, wrote:
On Thursday, October 18, 2012 9:25:33 AM UTC-7, sumesh56 wrote: ;1606468 Wrote: On Wednesday, October 17, 2012 10:25:30 AM UTC-7, sumesh56 wrote:- ;1606419 Wrote: - On Tuesday, October 16, 2012 10:30:23 AM UTC-7, sumesh56 wrote:-- -- i have a spreadsheet. The ENTRY and EXIT should tally.-- -- -- -- ENTRY----total of col A:D is in E2.-- -- -- -- EXIT---- total of col G:J is in K2.-- -- -- -- Cell K is formatted as E2=K2-- -- -- -- cell K2 is formatted as the sum of G:J-- -- -- -- when i enter a data wrongly in anywhere between the cells G:J a-- - warning- -- -- -- should come in col K2(of course after entering in all the four cells-- -- -- -- G:J).the idea is the number in entry should tally with that of the-- - exit.- -- -- -- is it possible?-- -- -- -- the excel file is attached.-- -- -- -- -- -- -- -- -- -- -- -- +-------------------------------------------------------------------+-- -- -- -- |Filename: entry and exit.zip |-- -- -- -- |Download:-- - http://www.excelbanter.com/attachment.php?attachmentid=626|- -- -- -- +-------------------------------------------------------------------+-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- sumesh56--- - - - See if this does what you want. - - Copy into the sheet module.- - - - Private Sub Worksheet_Change(ByVal Target As Range)- - Dim I As Integer, J As Integer- - I = Range("E3")- - J = Range("K3")- - If I < J Then- - MsgBox "E Does Not Equal K"- - End If- - End Sub- - - - Regards,- - Howard- thanks for the reply. but ,i am not able to understand what do you mean by sheet module. where can i find that? +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- sumesh56- Copy the code I sent, Ctrl + c, right click the sheet tab and click on View Code. This will take you to the vb editor. Paste, Ctrl + v, the code in the large white area. Now hit Alt + F11 to return to the worksheet. Test fly the code by making entries in notable cells. If E3 and K3 do not match you should get a Message Box alerting you. Note, in your post you mention E2 & K2 not matching. I assumed you meant E3 & K3. Regards, Howard hai Howard, thanks for the reply. i did your steps. but sorry,it does not work as i wished. pls see the attached excel file again. I have to enter the data of "entry" from col A:D and its total is in col E3. I would like to make entries A:D without getting any error message. the total should come in E3 of course without any error message. then comes to the next part"exit" entries of which will be in col G:J. here also I should be allowed to make entries in col G:J without EM. Now comes the main part. the total of G:J should come in col K3. Here in col K3 if the total does not equal E3 because of the entries in col G:J are wrong, I should get an EM. Now after doing the VB code,i get EM after each and every data entry in any one of the col from A:D and G:J. I have to hit the Enter button twice to get rid of the EM and to pass the cursor to the next cell. E3=K3 if it does not equal i should get the EM.I require something which can connect only with these columns and not with other columns. i think i could express my idea.thanks +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- sumesh56 Try this, assumes you will make all the entries and the activecell is K3 after all those entries are made. It works for me to start in cell A3 and after each entry use the "Right Arrow" key to advance throught the field and after the entry in J3, you will be in cell K3. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell < Range("K3") Then Exit Sub Dim I As Integer, J As Integer I = Range("E3") J = Range("K3") If I < J Then MsgBox "E Does Not Equal K" End If End Sub Regards, Howard OOOP's missed these two lines of code in my last reply. Add to the bottom of the code I just sent. End If End Sub |
#14
|
|||
|
|||
Quote:
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get a warning when data is entered wrongly
On Sunday, October 21, 2012 11:38:46 PM UTC-7, sumesh56 wrote:
;1606533 Wrote: On Thursday, October 18, 2012 1:44:49 PM UTC-7, wrote:- Try this, assumes you will make all the entries and the activecell is K3 after all those entries are made. It works for me to start in cell A3 and after each entry use the "Right Arrow" key to advance throught the field and after the entry in J3, you will be in cell K3. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell < Range("K3") Then Exit Sub Dim I As Integer, J As Integer I = Range("E3") J = Range("K3") If I < J Then MsgBox "E Does Not Equal K" End If End Sub Regards, Howard- OOOP's missed these two lines of code in my last reply. Add to the bottom of the code I just sent. End If End Sub these two phrases are already in the code. anyhow as you suggested i added these to the code for the second time and excel says "only comments may come after endsub, end function or end property" +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- sumesh56 Okay, my mistake. Remove the two lines and try the code. Regards, Howard |
#16
|
|||
|
|||
Quote:
|
#17
|
|||
|
|||
Quote:
|
#18
|
|||
|
|||
Quote:
Apologies for a delay in response. I wasn't feeling well and couldn't see your message. I thought you are always going to use only 1 row. Here is a small edit that you need to make in the code. replace the worksheet_change code in the thisworkbook object with the following code. Private Sub Worksheet_Change(ByVal Target As Range) Dim CheckRng As Range Dim EntryRng As Range Dim ExitRng As Range Set EntryRng = Range(Cells(Target.Row, 1), Cells(Target.Row, 4)) Set ExitRng = Range(Cells(Target.Row, 7), Cells(Target.Row, 10)) Set CheckRng = Application.Union(EntryRng, ExitRng) If Not Application.Intersect(Target, CheckRng) Is Nothing Then If Application.WorksheetFunction.CountBlank(EntryRng) = 0 And Application.WorksheetFunction.CountBlank(ExitRng) = 0 Then If Application.WorksheetFunction.Sum(EntryRng) < Application.WorksheetFunction.Sum(ExitRng) Then MsgBox "The Entry Total is not matching with Exit Total", vbCritical, "Error" Application.EnableEvents = False Application.Undo Application.EnableEvents = True Exit Sub End If End If End If End Sub Hope this helps. Prashant |
#19
|
|||
|
|||
Quote:
i copied the original worksheet of mine Entryexit. then i went for view code. then i pasted your presentcode. p alt f11. saved the doc as macro enabled. when i checked, it does not work as expected. it gives "syntex error" (visual basic) whenever i type something on row2. Last edited by sumesh56 : November 5th 12 at 06:07 PM |
#20
|
|||
|
|||
Quote:
Try these steps. 1) Go to the sheet where you will enter data. 2) on the sheet tab right click 3) Click on View Code 4) It will take you to the VBE window. 5) In the code window (right pane) paste my code. If still unsure send me your file and I will send you the updated file. Thanks, Prashant |
#21
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for wrongly entered 9 digit numbers | Excel Programming | |||
Using a Macro in Excel 2004 to move entered data from one sheet toanother and space between rows when next data is entered? | Excel Programming | |||
List Box - Option If wrongly selected | Excel Programming | |||
Time displaying wrongly | Excel Discussion (Misc queries) | |||
sorry i put wrongly for padded leading 0 | Excel Programming |