message box help
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: message box help

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    157
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com