Message box if cell is greater than less than 0 [Zero]

[SG]

Can someone help me with a code that can display a message box if a cell is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the column to see
if anything other than zero is there. If it is I have to go back and correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me right then so
I can make the change I need.

All the best,

SG

 

[JimMay]

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain
columns capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value **other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH

 

[SG]

Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each sheet I have
and there are many, are quit large and for my use I feel a message box would
better suite my needs. To give you an idea what I'm doing is, lets say in
Column C all cells have a negative number...Example ($50.00) now I enter in
that same amount in a different Column and this could be many Columns over
out of site of the page. If I make a mistake on the amount I put in then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain columns
capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value **other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH

Can someone help me with a code that can display a message box if a cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the column to
see
if anything other than zero is there. If it is I have to go back and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me right then
so
I can make the change I need.

All the best,

SG

 

[JimMay]

SG:
I've been trying now to understand VBA now for a couple of years,
I'm not certain that this is correct, but you might try it <<back up
Your work, please before trying>> The code below should be Copied and
Pasted into the "ThisWorkbook" code module of your workbook. It should
Fire (trigger) each time you make a change in cells C2:W1000 on any/all
Worksheets in the WB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Set myRng = Range("C2:W1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "You are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Let me know if this helps.
Jim May

Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each sheet I have
and there are many, are quit large and for my use I feel a message box would
better suite my needs. To give you an idea what I'm doing is, lets say in
Column C all cells have a negative number...Example ($50.00) now I enter in
that same amount in a different Column and this could be many Columns over
out of site of the page. If I make a mistake on the amount I put in then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain columns
capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value **other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH

Can someone help me with a code that can display a message box if a cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the column to
see
if anything other than zero is there. If it is I have to go back and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me right then
so
I can make the change I need.

All the best,

SG

 

[SG]

Jim,

You nailed it my friend. Thank you so much for taking the time to help on
this one.
I did change one little thing...
MsgBox "You are out of Balance" now reads

MsgBox " Jim May says you are out of Balance" VBG :>)

Thanks again,
SG

SG:
I've been trying now to understand VBA now for a couple of years,
I'm not certain that this is correct, but you might try it <<back up
Your work, please before trying>> The code below should be Copied and
Pasted into the "ThisWorkbook" code module of your workbook. It should
Fire (trigger) each time you make a change in cells C2:W1000 on any/all
Worksheets in the WB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Set myRng = Range("C2:W1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "You are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Let me know if this helps.
Jim May

Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each sheet I
have
and there are many, are quit large and for my use I feel a message box
would
better suite my needs. To give you an idea what I'm doing is, lets say in
Column C all cells have a negative number...Example ($50.00) now I enter
in
that same amount in a different Column and this could be many Columns
over
out of site of the page. If I make a mistake on the amount I put in then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain
columns
capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value **other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH


Can someone help me with a code that can display a message box if a
cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the column
to
see
if anything other than zero is there. If it is I have to go back and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me right
then
so
I can make the change I need.

All the best,

SG

 

[JimMay]

Note that I also anticipated that you might have multiple occurances of
your
Search criteria; Test it out <<carefully>> before "fully- adopting..
Thanks for the feedback,
Jim May

Jim,

You nailed it my friend. Thank you so much for taking the time to help on
this one.
I did change one little thing...
MsgBox "You are out of Balance" now reads

MsgBox " Jim May says you are out of Balance" VBG :>)

Thanks again,
SG

SG:
I've been trying now to understand VBA now for a couple of years,
I'm not certain that this is correct, but you might try it <<back up
Your work, please before trying>> The code below should be Copied and
Pasted into the "ThisWorkbook" code module of your workbook. It should
Fire (trigger) each time you make a change in cells C2:W1000 on any/all
Worksheets in the WB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Set myRng = Range("C2:W1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "You are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Let me know if this helps.
Jim May

Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each sheet I
have
and there are many, are quit large and for my use I feel a message box
would
better suite my needs. To give you an idea what I'm doing is, lets say in
Column C all cells have a negative number...Example ($50.00) now I enter
in
that same amount in a different Column and this could be many Columns
over
out of site of the page. If I make a mistake on the amount I put in then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain
columns
capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value **other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH


Can someone help me with a code that can display a message box if a
cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the column
to
see
if anything other than zero is there. If it is I have to go back and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me right
then
so
I can make the change I need.

All the best,

SG

 

[SG]

Jim,

I spoke to soon :<(
The only problem is when I enter my first amount in Column C the message box
appears. This should only happen when the second amount is entered and
doesn't match the first.

All the best,
SG

Note that I also anticipated that you might have multiple occurances of
your
Search criteria; Test it out <<carefully>> before "fully- adopting..
Thanks for the feedback,
Jim May

Jim,

You nailed it my friend. Thank you so much for taking the time to help on
this one.
I did change one little thing...
MsgBox "You are out of Balance" now reads

MsgBox " Jim May says you are out of Balance" VBG :>)

Thanks again,
SG

SG:
I've been trying now to understand VBA now for a couple of years,
I'm not certain that this is correct, but you might try it <<back up
Your work, please before trying>> The code below should be Copied and
Pasted into the "ThisWorkbook" code module of your workbook. It should
Fire (trigger) each time you make a change in cells C2:W1000 on any/all
Worksheets in the WB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Set myRng = Range("C2:W1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "You are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Let me know if this helps.
Jim May



Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each sheet
I
have
and there are many, are quit large and for my use I feel a message box
would
better suite my needs. To give you an idea what I'm doing is, lets say
in
Column C all cells have a negative number...Example ($50.00) now I
enter
in
that same amount in a different Column and this could be many Columns
over
out of site of the page. If I make a mistake on the amount I put in
then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain
columns
capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value
**other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH


Can someone help me with a code that can display a message box if a
cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the
column
to
see
if anything other than zero is there. If it is I have to go back
and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me
right
then
so
I can make the change I need.

All the best,

SG

 

[Guest]

SG,
I understand. I'll have to think about this (for a while...). Until then
at least you are promted that you have just set up an out of balance
condition,
which will be fixed only when you enter the values "mate" be it neg or
positive.
LOL...
Jim

Jim,

I spoke to soon :<(
The only problem is when I enter my first amount in Column C the message box
appears. This should only happen when the second amount is entered and
doesn't match the first.

All the best,
SG

Note that I also anticipated that you might have multiple occurances of
your
Search criteria; Test it out <<carefully>> before "fully- adopting..
Thanks for the feedback,
Jim May

Jim,

You nailed it my friend. Thank you so much for taking the time to help on
this one.
I did change one little thing...
MsgBox "You are out of Balance" now reads

MsgBox " Jim May says you are out of Balance" VBG :>)

Thanks again,
SG

SG:
I've been trying now to understand VBA now for a couple of years,
I'm not certain that this is correct, but you might try it <<back up
Your work, please before trying>> The code below should be Copied and
Pasted into the "ThisWorkbook" code module of your workbook. It should
Fire (trigger) each time you make a change in cells C2:W1000 on any/all
Worksheets in the WB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Set myRng = Range("C2:W1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "You are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Let me know if this helps.
Jim May



Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each sheet
I
have
and there are many, are quit large and for my use I feel a message box
would
better suite my needs. To give you an idea what I'm doing is, lets say
in
Column C all cells have a negative number...Example ($50.00) now I
enter
in
that same amount in a different Column and this could be many Columns
over
out of site of the page. If I make a mistake on the amount I put in
then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain
columns
capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value
**other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH


Can someone help me with a code that can display a message box if a
cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the
column
to
see
if anything other than zero is there. If it is I have to go back
and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me
right
then
so
I can make the change I need.

All the best,

SG

 

[Guest]

Here's a quick answer -- The below REPLACES my previous code (so Remove it).
This is a simple macro - I'd add a Form - Command-button and assign it to
the macro:
Then (as often as you like - whenever) click the button - it will tell you
if you have
a problem or not...
Hope this helps

Sub testbalance()
Set myRng = Range("C:W") ' All rows 1-65536
MyTotal = WorksheetFunction.Sum(myRng)
If MyTotal <> 0 Then
MsgBox "You have an Out of Balance condition"
End If
End Sub

Jim,

I spoke to soon :<(
The only problem is when I enter my first amount in Column C the message box
appears. This should only happen when the second amount is entered and
doesn't match the first.

All the best,
SG

Note that I also anticipated that you might have multiple occurances of
your
Search criteria; Test it out <<carefully>> before "fully- adopting..
Thanks for the feedback,
Jim May

Jim,

You nailed it my friend. Thank you so much for taking the time to help on
this one.
I did change one little thing...
MsgBox "You are out of Balance" now reads

MsgBox " Jim May says you are out of Balance" VBG :>)

Thanks again,
SG

SG:
I've been trying now to understand VBA now for a couple of years,
I'm not certain that this is correct, but you might try it <<back up
Your work, please before trying>> The code below should be Copied and
Pasted into the "ThisWorkbook" code module of your workbook. It should
Fire (trigger) each time you make a change in cells C2:W1000 on any/all
Worksheets in the WB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Set myRng = Range("C2:W1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "You are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Let me know if this helps.
Jim May



Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each sheet
I
have
and there are many, are quit large and for my use I feel a message box
would
better suite my needs. To give you an idea what I'm doing is, lets say
in
Column C all cells have a negative number...Example ($50.00) now I
enter
in
that same amount in a different Column and this could be many Columns
over
out of site of the page. If I make a mistake on the amount I put in
then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in certain
columns
capture
Info that is "going-on" down below.. For example in Cell F1 enter:
=Sum(M:M), where M is your formula column,, If F1 has a value
**other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH


Can someone help me with a code that can display a message box if a
cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the
column
to
see
if anything other than zero is there. If it is I have to go back
and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me
right
then
so
I can make the change I need.

All the best,

SG

 

[SG]

Jim,

Thanks for all your help. Still not what I'm looking for and it may not be
possible, but I truly appreciate your efforts.

All the best,
SG

Here's a quick answer -- The below REPLACES my previous code (so Remove
it).
This is a simple macro - I'd add a Form - Command-button and assign it to
the macro:
Then (as often as you like - whenever) click the button - it will tell you
if you have
a problem or not...
Hope this helps

Sub testbalance()
Set myRng = Range("C:W") ' All rows 1-65536
MyTotal = WorksheetFunction.Sum(myRng)
If MyTotal <> 0 Then
MsgBox "You have an Out of Balance condition"
End If
End Sub

Jim,

I spoke to soon :<(
The only problem is when I enter my first amount in Column C the message
box
appears. This should only happen when the second amount is entered and
doesn't match the first.

All the best,
SG

Note that I also anticipated that you might have multiple occurances of
your
Search criteria; Test it out <<carefully>> before "fully- adopting..
Thanks for the feedback,
Jim May


Jim,

You nailed it my friend. Thank you so much for taking the time to help
on
this one.
I did change one little thing...
MsgBox "You are out of Balance" now reads

MsgBox " Jim May says you are out of Balance" VBG :>)

Thanks again,
SG

SG:
I've been trying now to understand VBA now for a couple of years,
I'm not certain that this is correct, but you might try it <<back up
Your work, please before trying>> The code below should be Copied
and
Pasted into the "ThisWorkbook" code module of your workbook. It
should
Fire (trigger) each time you make a change in cells C2:W1000 on
any/all
Worksheets in the WB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
Set myRng = Range("C2:W1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "You are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Let me know if this helps.
Jim May



Hi Jim,

Not really what I need.
I use the Data Form to help cut down on all the scrolling. Each
sheet
I
have
and there are many, are quit large and for my use I feel a message
box
would
better suite my needs. To give you an idea what I'm doing is, lets
say
in
Column C all cells have a negative number...Example ($50.00) now I
enter
in
that same amount in a different Column and this could be many
Columns
over
out of site of the page. If I make a mistake on the amount I put in
then
this is where I need to know right away.



All the best,
SG

Not sure of your situation, but I often (in what you describe) do
a
Window-Freeze (freezing the First 4 or 5 rows (1:5) and in
certain
columns
capture
Info that is "going-on" down below.. For example in Cell F1
enter:
=Sum(M:M), where M is your formula column,, If F1 has a value
**other
than zero** - I have a problem.
What do you think?
An alternative before continuing into code
HTH


Can someone help me with a code that can display a message box
if a
cell
is
greater than less than 0 [Zero]

I have a column with each cell containing the following...

=SUM(C2:W2)
=SUM(C3:W3)
=SUM(C4:W4)
=SUM(C5:W5)
and so on

At the end of the day I have to scroll over and look down the
column
to
see
if anything other than zero is there. If it is I have to go back
and
correct
my typo mistakes.

I need VB code when I enter data into cells that will alert me
right
then
so
I can make the change I need.

All the best,

SG