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

[Carlton]

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

 

[Bob Phillips]

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)

 

[Harald Staff]

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

 

[Tom Ogilvy]

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

 

[Carlton Patterson]

Hi Tom,

A formua will change the value.

Cheers mate.

Carlton

 

[Carlton Patterson]

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

 

[Carlton Patterson]

Hi Tom,

A formua will change the value.

Cheers mate.

Carlton

 

[Bob Phillips]

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)

 

[Otto Moehrbach]

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]

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

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Harald Staff]

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]

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

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Bob Phillips]

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]

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