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.

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

thanks for the reply. but ,i am not able to understand what do you mean by sheet module. where can i find that?

i googled and find out the following.
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.

Hi,

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

thanks for the reply.I want the user to not proceed until the two totals match .of course i am not able to do it. kindly help with the VBA code.and give me detailed instructions as how to use it in the said excel sheet.

how to get a warning when data is entered wrongly
 

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

the unwanted icon upon A:D .how can i remove it?

somehow this post has a duplicate post. how can i delete the other one?

Hi,

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

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

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

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"

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

thanks for the code. it does works . but sorry, only for the first line only. what i mean is it works as i wished for the row A3. but when I enter data in row A4., nothing happens. assumes that the code works only for A3 row only. kindly modify it so that it can work for the whole of the worksheet.

sorry it does not work.

Hi,

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

i am not able to understand your idea. do you mean to say that i must prefix your present code with that of the old one. or i should do it afresh? i see something like worksheet on the left pane and change on the right pane when i open the view code dialog. what should i do? click on change? and put your new code? however i did this -



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.

Hi,

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

thanks prashant.but i am sorry i am not able to find the result. now i am sending you the file again. pls check yourself whether it works or not. the code should work for the entire rows of the worksheet. pls direct me what should be the steps.