+ Reply to Thread
Results 1 to 6 of 6

Data Validation Error Message not Working

  1. #1
    Registered User
    Join Date
    02-08-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    3

    Data Validation Error Message not Working

    Hi Guys,
    1st post so be gentle.
    I'm currently building a simple spreadsheet that tracks time associated with Doctors consultation item numbers. Each item number runs for a specific amount of time and over the course of the day a Doctor cant exceed a certain amount of hours or else they will be audited by the government. So to stop this from happening I have created a spreadsheet that will track their time and issue a warning message once they reached a time frame or exceeded it.
    So I have a column that lists each patients name on the far left and columns for each item number to the right of that. When a Doctor sees a patient they insert a 1 in the cell next to the patient and under the correct item number column. Items Numbers vary in time frames from 5 minutes to 30 minutes. At the bottom I have created a subtotal for each column the formula for that is =(SUM(B5:B163))*5 which multiplies the 1 the doctor inserts x 5 which equates to 5minutes. At the far right of the subtotals I have a "Totals" cell which lets the Doctor see the time he has accrued from the consultations, the formula for that is =SUM(B164:L164) which obviously adds up the subtotals.
    The problem I'm having is with the error message which I have associated with the "Totals" cell. I have used data validation with these settings: whole number > ignore blank > greater than or equal to and minimum is 300 for 300 minutes. Error alert settings: show error alert is ticked > style is "Warning" >Title is Error > Message is You have exceeded the maximum number of minutes". At the moment I can exceed the 300 minutes and not get the warning but if i double click on the cell the message comes up. Any suggestions

    Thanks in advance
    Rob

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Data Validation Error Message not Working

    Can you attach a desensitised sample of your workbook?

  3. #3
    Registered User
    Join Date
    02-08-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: Data Validation Error Message not Working

    Here is the attachment(I think!). Thanks for your fast reply.
    Attached Files Attached Files

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,135

    Re: Data Validation Error Message not Working

    welcome to the forum, Rob. Data validation error would only occur if you key in or do a selection from the cell. for eg. if someone keys in over 300. that is why you get the error only when you double click on the cell and enter. it's akin to keying in.

    you might want to consider conditional formatting. say for eg the totals is at M164. go to Home -> Conditional Formatting -> Highlight Cell Rules -> More Rules -> change to Greater or Equals to -> 300
    format the color.

    alternatively, you might consider VBA. press ALT + F11 to go into the VBE. press CTRL + R to ensure you have the Project Explorer on the left. i would double-click the Sheet1 as that is the worksheet with the totals. you might have to choose your own worksheet the totals are in. once you double-click, the code window appears on the right side. paste this code in:
    Please Login or Register  to view this content.
    whenever something changes in this worksheet, it checks if M164 is greater or equals to 300. you have to enable macros after you open my file. and you can add a number in cell B153 for eg to see the prompt appears.

    ps: i did this before you attached the file and too lazy to re-do it.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Data Validation Error Message not Working

    DV works, if I type in defined range 2 or 3 or .... different than 1 it will show Error Msg
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  6. #6
    Registered User
    Join Date
    02-08-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: Data Validation Error Message not Working

    Thank you benishiryo so much for your help and effort.
    Cheers
    Last edited by rvhfry; 02-08-2018 at 09:56 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Data validation C2+D2 = B2 error message
    By bjprent in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-06-2016, 04:42 AM
  2. [SOLVED] Data Validation Error Message
    By Yoepy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-21-2015, 10:28 PM
  3. [SOLVED] Automate pop-up error message for data validation purposes
    By karl8695 in forum Excel General
    Replies: 2
    Last Post: 07-21-2014, 07:08 AM
  4. Replies: 6
    Last Post: 04-09-2012, 02:26 PM
  5. Data Validation Error Message
    By Blake 7 in forum Excel General
    Replies: 11
    Last Post: 11-28-2010, 01:52 PM
  6. Another Data Validation question:Error message
    By Jeff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2005, 02:05 PM
  7. Error Message with Data Validation
    By Chet Hurd in forum Excel General
    Replies: 2
    Last Post: 06-09-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1