how to get a warning when data is entered wrongly

[sumesh56]

i have a spreadsheet. The ENTRY and EXIT should tally.
ENTRY----total of col A 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 warnin
should come in col K2(of course after entering in all the four cell
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
+-------------------------------------------------------------------

 

[lhkittle]

i have a spreadsheet. The ENTRY and EXIT should tally.

ENTRY----total of col A 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|

+-------------------------------------------------------------------+

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

 

[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 mea
by sheet module. where can i find that

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[lhkittle]

thanks for the reply. but ,i am not able to understand what do you mean

by sheet module. where can i find that?





+-------------------------------------------------------------------+

+-------------------------------------------------------------------+

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

 

[sumesh56]

Copy the code I sent, Ctrl + c, right click the sheet tab and click o
View Code. This will take you to the vb editor. Paste, Ctrl + v, th
code in the large white area. Now hit Alt + F11 to return to th
worksheet.

Test fly the code by making entries in notable cells. If E3 and K3 d
not match you should get a Message Box alerting you.

Note, in your post you mention E2 & K2 not matching. I assumed yo
meant E3 & K3.

Regards,
Howard

hai Howard,
thanks for the reply. i did your steps. but sorry,it does not work as
wished.
pls see the attached excel file again. I have to enter the data o
"entry" from col A and its total is in col E3. I would like to mak
entries A without getting any error message. the total should come i
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. No
comes the main part. the total of G:J should come in col K3. Here in co
K3 if the total does not equal E3 because of the entries in col G:J ar
wrong, I should get an EM.
Now after doing the VB code,i get EM after each and every data entry i
any one of the col from A and G:J. I have to hit the Enter butto
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 whic
can connect only with these columns and not with other columns. i think
i could express my idea.thanks


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

 

[lhkittle]

(e-mail address removed);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 is in E2.--
--
--
--
EXIT---- total of col G:J is in K2.--
--

cells--

the--

-
exit.-

+-------------------------------------------------------------------+--

|--

+-------------------------------------------------------------------+--

--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-- --
--
--
--
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


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.


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 and its total is in col E3. I would like to make

entries A 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 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





+-------------------------------------------------------------------+

+-------------------------------------------------------------------+

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

 

[lhkittle]

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

 

[sumesh56]

-
K3 after all those entries are made. It works for me to start in cel
A3 and after each entry use the "Right Arrow" key to advance through
the field and after the entry in J3, you will be in cell K3.

OOOP's missed these two lines of code in my last reply. Add to th
bottom of the code I just sent.

End If
End Sub

these two phrases are already in the code. anyhow as you suggested
added these to the code for the second time and excel says "onl
comments may come after endsub, end function or end property

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[lhkittle]

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"





+-------------------------------------------------------------------+

+-------------------------------------------------------------------+

Okay, my mistake. Remove the two lines and try the code.

Regards,
Howard

 

[jack_n_bub]

Hi,

Attached is the updated file and hopefully contains everything you wer
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 section
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

Hi,

Apologies for a delay in response. I wasn't feeling well and couldn'
see your message.

I thought you are always going to use only 1 row. Here is a small edi
that you need to make in the code.

replace the worksheet_change code in the thisworkbook object with th
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 An
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.
Prashan

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[sumesh56]

Hi

Apologies for a delay in response. I wasn't feeling well and couldn'
see your message

I thought you are always going to use only 1 row. Here is a small edi
that you need to make in the code

replace the worksheet_change code in the thisworkbook object with th
following code

Private Sub Worksheet_Change(ByVal Target As Range
Dim CheckRng As Rang
Dim EntryRng As Rang
Dim ExitRng As Rang
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 The
If Application.WorksheetFunction.CountBlank(EntryRng) = 0 An
Application.WorksheetFunction.CountBlank(ExitRng) = 0 The
If Application.WorksheetFunction.Sum(EntryRng) <
Application.WorksheetFunction.Sum(ExitRng) The
MsgBox "The Entry Total is not matching with Exit Total"
vbCritical, "Error
Application.EnableEvents = Fals
Application.Und
Application.EnableEvents = Tru
Exit Su
End I
End I
End I
End Su

Hope this helps
Prashan

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


i copied the original worksheet of mine Entryexit. then i went for vie
code. then i pasted your presentcode. p alt f11. saved the doc as macr
enabled. when i checked
it does not work as expected. it gives "syntex error" (visual basic
whenever i type something on row2

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[jack_n_bub]

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



i copied the original worksheet of mine Entryexit. then i went for vie
code. then i pasted your presentcode. p alt f11. saved the doc as macr
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,
Prashan

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[sumesh56]

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
am sending you the file again. pls check yourself whether it works o
not. the code should work for the entire rows of the worksheet. pl
direct me what should be the steps

+-------------------------------------------------------------------
|Filename: entry and exit FOR TEST.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=707
+-------------------------------------------------------------------