message box help

[Nathan B]

I need help writing the following Macro:

I have some data that's computed let's say in cell A1 of an excel sheet. What I want to do is have a message box pop up when the numerical value from that cell goes over a certain limit.

eg. If cell A5's formula is =Sum(A1:A4). I want a warning message box to come up if A5>10.

I also need help writng a macro for another message box. In a sheet I'm working on, the user can input an expiration date in a certain cell (say A1). When that date has expired I want a message box to pop up telling the user that "The expiration date has expired".

Please help.

[lenze]

You can do this using VBA and the worksheet change event, but an easier way would be to use Data - Validation. In your example, validate A1:A4 using the custom formula A5>10. Input a message on the Error Alert Tab
Now if a number is entered in a cell in the range A1:A4 that causes A5 to exceed 10, the display will pop up.

[Nathan B]

Thanks for the help. It sounds like it should work but I'm having some problems. i validates cells A1:A4 with the custom formula A5>10 and entered an error alert message. However, any numbers entered into any one of the cells causes the alert to pop-up. I can enter 1 in cell A1 and the alert triggers. I can enter 22 in cell A3 and the alert triggers. What am I doing wrong?

Thanks

[boydr]

There can be 2 error messages in the validation. One is for when the cell is clicked on, which displays a custom message, and the other is for you're validation test A1>5 (or whatever), which displays a customized error message. Double check you're validation to see that you didn't make this mistake.

[Nathan B]

After I choose Data-Validation a box comes up with selection tabs at the top. They are Settings, Input Message, and Error Alert. On the settings tab I selected custom and in the formula bax that becomes active I input A5>10 (I also went back and tried =A5>10). On the Input message tab I unchecked the box that said "Show Input message when cell is selected ( I also tried it with this box checked). On the Error Alert tab I tried checking the box that reads "Show error alert after invalid data is entered" and I also removed the check from it. I filled in the title, style and error message portions on this tab. I still get the error message anytime something is entered. any other ideas?

[Yogi Anand]

On 2002-04-20 15:57, Nathan B wrote:
After I choose Data-Validation a box comes up with selection tabs at the top. They are Settings, Input Message, and Error Alert. On the settings tab I selected custom and in the formula bax that becomes active I input A5>10 (I also went back and tried =A5>10). On the Input message tab I unchecked the box that said "Show Input message when cell is selected ( I also tried it with this box checked). On the Error Alert tab I tried checking the box that reads "Show error alert after invalid data is entered" and I also removed the check from it. I filled in the title, style and error message portions on this tab. I still get the error message anytime something is entered. any other ideas?

Hi Nathan:
I have not followed this thread. I may be able to help in the Validation scheme you are trying to set up. If I understand you correctly, you are trying to set up a Validation Rule for entries in cell A1 through A4 ... the rule being that you can enter any thing in cells A1 to A4 as long as the entry in cell A5 is >10. If this is the case, then do the following:

1) Highlite cells A1:A4
2) DATA|VALIDATION|SETTINGS|ALLOW|CUSTOM|Formula

3) In the formula box enter the following

=$A$5>10

4) OK

To reiterate, with this validation rule setup, you will be able to enter any thing in cells A1 through A4, as long as entry in cell A5 is >10.

HTH

[Nathan B]

Thanks for all the help. I was able to use this info to solve this problem.