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

[Carlton Patterson]

Hi Bob,

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

Carlton

 

[Carlton Patterson]

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

 

[Harald Staff]

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(E2>A2,F2>B2,G2>C2),"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]

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

 

[Harald Staff]

Glad to hear that Carlton. Thanks for the feedback.

Best wishes Harald

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Harald Staff]

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)

 

[Harald Staff]

Suggestions to what ? If E2>A2 and F2>B2 and G2>C2 then the formula returns
YOU in I2, otherwise it returns nothing. Wasn't that what you asked for ?
Please explain.

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Carlton Patterson]

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

 

[Harald Staff]

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

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

 

[Harald Staff]

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:

(...)

 

[Carlton Patterson]

Hi Harald,

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

Cheers mate.

Carlton