Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Tom,

A formua will change the value.

Cheers mate.

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Tom,

A formua will change the value.

Cheers mate.

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

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.

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

To be honest it doesn't have to be a message box - but I do need some
kind of notification.

Basically, I trade the NYSE and I constantly monitor 500 stocks and I
need someway of being notified when certain conditions occur.

I would go through the conditions but I don't think you'd be interested.

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

OK,

I managed to get Bob's formula to automatically pop-up a message box by
changing the formula to :

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("A5") Me.Range("A1") And _
Me.Range("A6") Me.Range("A2") And _
Me.Range("A7") Me.Range("A3") Then
MsgBox "Damn it"
End If
End Sub

Now, if someone could just show how to apply it to a range of cells
instead of individual cells I think I would be on my way.

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi all,

I think I have it. If someone could just show me how to apply the
following program to a number of rows I think I'll be set.

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("A1") Me.Range("E1") And _
Me.Range("B1") Me.Range("F1") And _
Me.Range("C1") Me.Range("G1") Then
MsgBox "Damn it"
End If
End Sub


Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Is this what you want for say rows 5-10

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
Select Case .row
Case 5, 6, 7, 9, 10
If (Me.Cells(.row, "D").Value Me.Cells(.row, "A").Value
And _
Me.Cells(.row, "E").Value Me.Cells(.row, "B").Value
And _
Me.Cells(.row, "F").Value Me.Cells(.row, "C").Value)
Then
MsgBox "Darn in row " & .row
End If
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

although I would remove the Msgbox as Harald says and use conditional
formatting.

--

HTH

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


"Carlton Patterson" wrote in message
...
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 ***

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Bob,

While I check out your update I just thought I would let you know that I
would love to use conditional formatting, however it won't allow you to
run a macro or anything other than change the colour of a cell. I
currently use conditional formatting but it still requires that I
visually check the spread to see if a condition has occurred. During
trading hours I often don't have enough time to check therefore pop-up
or ability to run a macro would be ideal.

Cheers

Carlton


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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Bob,

I'm not getting any joy with the latest update - getting Compile error:
syntax error.

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi I'm going to call it a night. I look forward to any updates you care
to assist with in the morning.

Cheers guys

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Carlton

Ok, 500 rows and most out of screen, one dedicated user, yes that makes
sense.

Here's what I'd do:
Insert a new Row 1 so the first row of data is in row 2.
In cell I2 a formula like this:
=IF(AND(E2A2,F2B2,G2C2),"YO","")
Fill it down the rows as far as needed. Now the I column cell will display
YO in rows where the conditions are met. You can format this conditionally
or just plain awful -notifications are best at their worst.

Now in row 1, cell I1, this formula:
=COUNTIF(I2:I2000,"YO")

Now I1 will display the amount of YOs. Freeze row 1 and it'll always be
there.

Now if you really need some notification: Rightclick the sheet tab, "view
code", paste this in:

Private Sub Worksheet_Calculate()
If Range("I1").Value 0 Then
Beep
'or msgbox
'or play sound
'or send email
'or whatever
End If
End Sub

HTH. Best wishes Harald


"Carlton Patterson" skrev i melding
...
Hi Harald,

To be honest it doesn't have to be a message box - but I do need some
kind of notification.

Basically, I trade the NYSE and I constantly monitor 500 stocks and I
need someway of being notified when certain conditions occur.

I would go through the conditions but I don't think you'd be interested.

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Harald Staff,

You are a genius!

I don't know how to thank you. You truly have truly solved a big problem
for me. As I mentioned, each day I monitor all the stocks in the S&P500
you can only imagine have much time this program has saved me.

This is the first time I posted on this group, I can't belive how
helpful everybody is.

Cheers mate.

Carlton


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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Glad to hear that Carlton. Thanks for the feedback.

Best wishes Harald

"Carlton Patterson" skrev i melding
...
Harald Staff,

You are a genius!

I don't know how to thank you. You truly have truly solved a big problem
for me. As I mentioned, each day I monitor all the stocks in the S&P500
you can only imagine have much time this program has saved me.

This is the first time I posted on this group, I can't belive how
helpful everybody is.

Cheers mate.

Carlton


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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

I copied the formula to one row below, however the formula keeps on
triggering without any of the conditions actually being met.

Can you think of any reason why this is happening?

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Harald

I'm checking out the formula in a bit more detail - after one condition
is met if a value is changed in any other cell the formula is triggered,
regardless whether a condition is met in that row or not.

Any suggestions?


Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

Is there a way that the program could be edited to restart once a
condition is met? Or exit when a condition is met and not do anything
until another is met?

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

What is "Keeps on triggering" ? Formulas usually don't run in loops.
What does the formula read the one row below ? Does all row numbers adjust
to the row in question ?
Does anything happen when you press F9 ? (Assuming you are using Windows
here)


"Carlton Patterson" skrev i melding
...
Hi Harald,

I copied the formula to one row below, however the formula keeps on
triggering without any of the conditions actually being met.

Can you think of any reason why this is happening?

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Suggestions to what ? If E2A2 and F2B2 and G2C2 then the formula returns
YOU in I2, otherwise it returns nothing. Wasn't that what you asked for ?
Please explain.




"Carlton Patterson" skrev i melding
...
Harald

I'm checking out the formula in a bit more detail - after one condition
is met if a value is changed in any other cell the formula is triggered,
regardless whether a condition is met in that row or not.

Any suggestions?


Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

Lets say I have 50 rows and I copy the formula to all 50 rows. If, say
in row 5, a condition is met it will a trigger the message, which is
fine, however if a subsequent cell value changes,in say row 10, the
formula will trigger again whether the condition has been met in row 10
or not.

I think the problem lies with the line:

=COUNTIF(I2:I2000,"YO")


Once you get one "YO" the formula will trigger continously in all rows.

I hope I've explained myself well.

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

Lets say I have 50 rows and I copy the formula to all 50 rows. If, say
in row 5, a condition is met it will a trigger the message, which is
fine, however if a subsequent cell value changes,in say row 10, the
formula will trigger again whether the condition has been met in row 10
or not.

I think the problem lies with the line:

=COUNTIF(I2:I2000,"YO")


Once you get one "YO" the formula will trigger continously in all rows.

I hope I've explained myself well.

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

I read the following:

For multiple criteria in different fields, the COUNTIF function doesn't
work. However, you can use an array formula.

Is this correct? If so, can you show me how to incorporate an array
formula?

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

=COUNTIF(I2:I2000,"YO")
will, at all times, count and display the number of YOs in the I column.
That is all it does. It does not trig a messagebox, that is impossible and
you're supposed to know that by now.

All cell changes will trig the worksheets Calculate event though, so the
code will run every time a cell value chamges. So my code will make your
computer beep on all cell changes if there is a positive number in I1..

Did you put a MsgBox in the Calculate event, is that what this formula
trigging is about ? I told you two important things about MsgBoxes:
- A worksheet formula can not trig a messagebox.
- They are pretty useless and extremely annoying.

HTH. Best wishes Harald

"Carlton Patterson" skrev i melding
...
Hi Harald,

Lets say I have 50 rows and I copy the formula to all 50 rows. If, say
in row 5, a condition is met it will a trigger the message, which is
fine, however if a subsequent cell value changes,in say row 10, the
formula will trigger again whether the condition has been met in row 10
or not.

I think the problem lies with the line:

=COUNTIF(I2:I2000,"YO")


Once you get one "YO" the formula will trigger continously in all rows.

I hope I've explained myself well.

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

You're quite right I should know that by now.

I agree that msgbox's are extremely annoying but I can't think of any
other way to be notified of an event change.

I changed the formula to the following:


Private Sub Worksheet_Calculate()
If Range("I1").Value 0 Then
MsgBox "Here we GO!"
'or msgbox
'or play sound
'or send email
'or whatever
End If
End Sub

Cheers

Carlton

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

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Well change it back. It is pretty useless as is. The event change in
question is the sheet's Calculate event, indicating that some cell value in
an unknown location just changed from one unknown thing to another.

This is really four tasks in one. Forget about formulas and boxes and forums
for a while. Sit back and consider carefully the following:

- You want to be notified when ?
- You want the notification to tell you what ?
- You want to be notified how ?
- You respond/react to it how ? What happens ?

HTH. Best wishes Harald


I agree that msgbox's are extremely annoying but I can't think of any
other way to be notified of an event change.

I changed the formula to the following:
(...)

Is it Possible to create a Message Box (MSGBOX) with Excel Formula?
 

Hi Harald,

You're right. I need to investigate a little further.

Cheers mate.

Carlton

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