+ Reply to Thread
Results 1 to 7 of 7

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,147

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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

  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.

  7. #7
    Registered User
    Join Date
    12-13-2022
    Location
    New York
    MS-Off Ver
    10
    Posts
    10

    Re: Data Validation Error Message not Working

    Select the cell that contains a data validation list
    On the Ribbon, click the Data tab
    Click the top of the Data Validation button, to open the dialog box
    In the Data Validation window, go to the Settings tab
    Add a check mark to the In-cell dropdown check box
    Click the OK button

    Greetings,
    Peter

+ 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. [SOLVED] 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