Share via

Validation Not Triggering

Anonymous
2020-07-11T19:07:32+00:00

I have a date field with Validation set to dates between with a date picker UserForm.

When I select the date from the date picker and populate the cell it does not trigger the validation check.

I have unchecked the Ignore Blanks on the validation settings but it is still not triggering?

Help would be appreciated.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. OssieMac 47,981 Reputation points Volunteer Moderator
    2020-07-13T06:08:27+00:00

    Unfortunately Data Validation does not work if the value is inserted with VBA code. The validation only works if the User inserts the required value in the interactive mode on the worksheet.

    Also if you copy a value and paste the value into the Data Validation cell then the validation does not work. 

    Plus if a cell is copied and pasted (ie. standard paste) then the Data Validation is actually over written and gone altogether.

    You will need to include some VBA code in an If statement to perform the validation immediately prior to the code that inserts the value. Since you are using VBA code for the process then I assume you will know how to insert the If statement to validate. However, if you need more assistance with this then please post the section of code that writes the value to the worksheet and I will assist further.

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-07-12T08:06:53+00:00

    Hi Perry,

    As you mentioned you were using a date picker from a UserForm, I would like to confirm with you whether the query related to VBA. If so, you may provide your VBA here so that community members could check the details of the VBA.

    If I misunderstood what you mean, please feel free to point it out, and it would be great if you could provide some more details like screenshots to this issue.

    Best regards,

    Madoc

    0 comments
  2. Anonymous
    2020-07-12T11:38:56+00:00

    Hi Madoc,

    Thank you for getting back to me.

    Yes this is related to VBA, I have included some screenshots.

    If I manually input an incorrect date the validation gets triggered.

    If the Date Picker inputs an incorrect date the validation does not get triggered.

    If you need more information please let me know.

    Regards,

    Perry

    0 comments
  3. Anonymous
    2020-07-13T10:59:26+00:00

    Hi OssieMac,

    Thank you very much for the confirmation of my suspicions on the Validation Trigger and you offer to assist with the workaround code.

    I was able to create a work around that I would like to share with the community.

    Regards,

    Perry

    0 comments
  4. OssieMac 47,981 Reputation points Volunteer Moderator
    2020-07-14T00:41:57+00:00

    As per my previous reply, I felt sure you would have the expertise to edit the code. 

    Just another thought. You might like to investigate if you can edit the code for your CalendarFrm so that it will only display dates between the variables YrStartDate and YrEndDate so the User cannot select an invalid date.

    Also, if you are satisfied that my previous reply answered the original question, don't forget to mark it as the answer.

    0 comments