Hello all,

I wonder if someone would be so kind as to let me know how I can
program excel to trigger a message box when three conditions occur.
For example, lets say I have the following values in cells A1 to A3.


Cell A1 = 2
Cell A2 = 3
Cell A4 = 4

Now, I would like a message box (msgbox) to be triggered only if the
following are ALL greater than the above cells:

Cell A5 = 3
Cell A6 = 4
Cell A7 = 5

So, if cells A5 through to A7 are greater than their respective cells
A1 through to A3 then I would like a formula that would trigger a
message box.

Please note that all cells A5 through to A7 will need to be higher.
For example, if cell A5 is greater than cell A1 and cell A6 is greater
than cell A2, but cell A7 is less than A4 I don't want a message box
to be triggered.

Any help would be greatly appreciated.

Even if someone could tell me if its actually possible (which I'm sure
it is) I would be grateful.

Cheers

Carlton

One way

Private Sub Worksheet_Calculate()
If Me.Range("A5") Me.Range("A1") And _
Me.Range("A6") Me.Range("A2") And _
Me.Range("A7") Me.Range("A3") Then
MsgBox "Thhis is it"
End If
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)


"Carlton" wrote in message
om...
Hello all,

I wonder if someone would be so kind as to let me know how I can
program excel to trigger a message box when three conditions occur.
For example, lets say I have the following values in cells A1 to A3.


Cell A1 = 2
Cell A2 = 3
Cell A4 = 4

Now, I would like a message box (msgbox) to be triggered only if the
following are ALL greater than the above cells:

Cell A5 = 3
Cell A6 = 4
Cell A7 = 5

So, if cells A5 through to A7 are greater than their respective cells
A1 through to A3 then I would like a formula that would trigger a
message box.

Please note that all cells A5 through to A7 will need to be higher.
For example, if cell A5 is greater than cell A1 and cell A6 is greater
than cell A2, but cell A7 is less than A4 I don't want a message box
to be triggered.

Any help would be greatly appreciated.

Even if someone could tell me if its actually possible (which I'm sure
it is) I would be grateful.

Cheers

Carlton

Hi mate,

Thanks for getting back with a solution. I was wondering if you could
take it one step a further and show me how I could apply the formula to
a cells in different rows?

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

I presume that you mean different columns.

This will check columns A, B and C

Private Sub Worksheet_Calculate()
With Me.Range("A5")
If .Value .Offset(-4,0) And _
.Offset(1,0).Value .Offset(-3,0) And _
.Offset(2,0).Value .Offset(-2,0)Then
MsgBox "Thhis is it for " & .Address
End If
End With
With Me.Range("B5")
If .Value .Offset(-4,0) And _
.Offset(1,0).Value .Offset(-3,0) And _
.Offset(2,0).Value .Offset(-2,0)Then
MsgBox "Thhis is it for " & .Address
End If
End With

With Me.Range("C5")
If .Value .Offset(-4,0) And _
.Offset(1,0).Value .Offset(-3,0) And _
.Offset(2,0).Value .Offset(-2,0)Then
MsgBox "Thhis is it for " & .Address
End If
End With

End Sub


--

HTH

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


"Carlton Patterson" wrote in message
...
Hi mate,

Thanks for getting back with a solution. I was wondering if you could
take it one step a further and show me how I could apply the formula to
a cells in different rows?

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Carlton
You have to be more definitive than "cells in different rows". What
cells in what different rows? Excel can work with relative cells. For
instance, the active cell and the two cells below it. Or the two cells to
the right. Or the cells 5 and 27 cells to the right. Etc. Explain it like
you would to someone just walking in off the street. HTH Otto
"Carlton Patterson" wrote in message
...
Hi mate,

Thanks for getting back with a solution. I was wondering if you could
take it one step a further and show me how I could apply the formula to
a cells in different rows?

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Hi,

Sorry for not being very decriptive.

Let me try to explain;

Lets say i have the following numbers in row 4,

A4 = 5, B4 = 6, C4 = 7

I would like excel to trigger a message box if the values in the same
row are greater e.g D4 5, E4 6, F4 7.

Now, if you can do that for one row, I was wondering if I could apply
the formuala to many rows.

Thanks.

Carlton.

P.S. This is the first time I posted here, you guys are really helpful.

Cheers



*** Sent via Developersdex http://www.developersdex.com ***

Hi Carlton

Why do you want a messagebox ? Those are pretty useless for getting
information through, and also extremely annoying. Please explain what good
this is supposed to do.

Best wishes Harald

"Carlton Patterson" skrev i melding
...

I would like excel to trigger a message box if the values in the same
row are greater e.g D4 5, E4 6, F4 7.

Now, if you can do that for one row, I was wondering if I could apply
the formuala to many rows.

Hi Bob Phillips,

Thanks for helping out mate, however I'm not sure how to get the program
to work.

I'll keep on checking it out but if you could let me know where I need
to input the data I'd appreciate it.

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Hi Bob Phillips,

Thanks for helping out mate, however I'm not sure how to get the program
to work.

I'll keep on checking it out but if you could let me know where I need
to input the data I'd appreciate it.

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Hi Bob,

I managed to get the first formula to work. Is it possible to get it to
activate when formula updates the cell?

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Hi mate,

Thanks for getting back with a solution. I was wondering if you could
take it one step a further and show me how I could apply the formula to
a cells in different rows?

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Hi Carlton

Only a macro can display a custom messagebox. And an Excel formula can not
start a macro. So the theoretical answer is No, a formula can not display a
message box.

But Excel has lots of "events", which are small macros running when this and
that happens, like cell entries, selection changes, calculations, ... So
what you want don is surely possible. Have alook at
http://www.cpearson.com/excel/events.htm
for some background.

HTH. Best wishes Harald


"Carlton" skrev i melding
om...
Hello all,

I wonder if someone would be so kind as to let me know how I can
program excel to trigger a message box when three conditions occur.
For example, lets say I have the following values in cells A1 to A3.


Cell A1 = 2
Cell A2 = 3
Cell A4 = 4

Now, I would like a message box (msgbox) to be triggered only if the
following are ALL greater than the above cells:

Cell A5 = 3
Cell A6 = 4
Cell A7 = 5

So, if cells A5 through to A7 are greater than their respective cells
A1 through to A3 then I would like a formula that would trigger a
message box.

Please note that all cells A5 through to A7 will need to be higher.
For example, if cell A5 is greater than cell A1 and cell A6 is greater
than cell A2, but cell A7 is less than A4 I don't want a message box
to be triggered.

Any help would be greatly appreciated.

Even if someone could tell me if its actually possible (which I'm sure
it is) I would be grateful.

Cheers

Carlton

I assume this is to occur when one of the values change. What will change a
value

User manually edits the cell
Formula Updates
DDE update
Something else

--
Regards,
Tom Ogilvy

"Carlton" wrote in message
om...
Hello all,

I wonder if someone would be so kind as to let me know how I can
program excel to trigger a message box when three conditions occur.
For example, lets say I have the following values in cells A1 to A3.


Cell A1 = 2
Cell A2 = 3
Cell A4 = 4

Now, I would like a message box (msgbox) to be triggered only if the
following are ALL greater than the above cells:

Cell A5 = 3
Cell A6 = 4
Cell A7 = 5

So, if cells A5 through to A7 are greater than their respective cells
A1 through to A3 then I would like a formula that would trigger a
message box.

Please note that all cells A5 through to A7 will need to be higher.
For example, if cell A5 is greater than cell A1 and cell A6 is greater
than cell A2, but cell A7 is less than A4 I don't want a message box
to be triggered.

Any help would be greatly appreciated.

Even if someone could tell me if its actually possible (which I'm sure
it is) I would be grateful.

Cheers

Carlton

Hi Tom,

A formua will change the value.

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom,

A formua will change the value.

Cheers mate.

Carlton

*** Sent via Developersdex http://www.developersdex.com ***