Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

**inoka** · 04-06-2018, 04:00 AM

Hi,
this is related with following solved post.

https://www.excelforum.com/excel-pro...ml#post4802808

In here,I need following validation also.
When copy and paste data from another worksheet or workbook under the "Date" field column, I need to automatically clear all the invalid dates.

Please Login or Register  to view this content.

When I trying to add invalid date into the cell, This code is working properly.
When copying and pasting data from another sheet or workbook, this is allowed to paste invalid dates. Therefore, I need to automatically clear all invalid dates while copy and pasting data.

**kev_** · 04-06-2018, 04:41 AM

@inoka
Forum member jindon was very patient and helped you a lot on the other thread

You are making the code very complicated because you are not controlling what is input into your spreadsheet - which is bad practice

Every time you find a new problem perhaps the code needs adjusting again
The user is entering data that the code is then rejecting which is a waste of effort
I will help you in a different way
Instead of making the code more complicated perhaps it would be better to help you make the input better

Why is the input bad?
How is the user inputting dates?
Are you using data validation?
Is the user copying and pasting bad dates from somewhere else?

Making the input good
Try the attached workbook
Try to enter any date in column B that is before today - it will not let you
- but you can enter tomorrow's date etc
- look at data validation on the cell to see how that works
- use a different validation to suit your needs
- perhaps the user could select date from a dropdown etc
Try to copy and paste something into column B - it will not let you
- VBA protects the data validation
- see code below to see how that works

Place code in sheet module

Please Login or Register  to view this content.

**jindon** · 04-06-2018, 04:52 AM

Please Login or Register  to view this content.

**inoka** · 04-07-2018, 12:11 PM

Hi jindon,
thank you so much for your response. your previous code was more helpful for me. It's great.
This time, I need to enhance that code to following .
* When copying and pasting data from another sheet or workbook to this date columns, I need to automatically clear all invalid data which has invalid date format.

Ex: copy and paste bulk of data to the K column. (this column is date field and it has validated as DD/MM/YYYY format by using your code - (refer below code))
among these bulk of copied data, there can be invalid date format data. (ex: 14-09-2019, 09.2.2019, 4/26/2018 etc)
Then, after copy and paste, I need to automatically clear invalid data which are not matched with following code validation)

Please Login or Register  to view this content.

Hope you get my problem clearly.

**AliGW** · 04-07-2018, 12:21 PM

You have not responded to post #2 yet.

**inoka** · 04-07-2018, 01:09 PM

Hi AliGW ,
I cannot understand it clearly.

So, I posted my problem clearly with post #5.

**kev_** · 04-07-2018, 03:41 PM

What is not clear?
You have BAD data

Why is your data BAD?

Can you make the data GOOD?

**jindon** · 04-07-2018, 07:56 PM

Upload a workbook with data now and the data to be pasted, so I can test it.

Logically my code should check all the data within intersect of columns in question and pasted range.

**inoka** · 04-08-2018, 11:39 AM

Hi jindon,
Thank you so much for your response. As you requested, I have attached excel workbook. It has included all data and validations.

**jindon** · 04-08-2018, 08:36 PM

OOps...

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

**inoka** · 04-09-2018, 01:55 AM

Hi jindon,
wooowww, it's working. Thank you soooo much.

**jindon** · 04-09-2018, 01:58 AM

No problem.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**inoka** · 04-09-2018, 02:01 AM

Hi jindon,
Could you please help me to find solution to the following.
Same as the date validation, I need to automatically clear invalid data over data validation cells. My attached sheet (Post #9) is included data validation column. (Column "L").
When copy and pasting data from another source, I need to automatically remove invalid data which is not specified with data validation.

It's great, if I can find the solution for this problem too..

**jindon** · 04-09-2018, 02:32 AM

Do you want it like this?

Please Login or Register  to view this content.

**inoka** · 04-09-2018, 02:38 AM

Hi jindon,
Thank you sooo much. But, there is an issue. When copy and pasting , 2nd validation automatically converted to 1st validation data. Please find the attached excel sheet. How can I solve this. When Copy and paste, automatically clear data and cell has converted as data validation cell.

**jindon** · 04-09-2018, 02:40 AM

Did you ask same question in your new thread?

https://www.excelforum.com/excel-pro...ion-cells.html

**inoka** · 04-09-2018, 02:52 AM

Hi jindon,
Yes

**jindon** · 04-09-2018, 02:58 AM

Then you should wait for the reply there.

Mark this thread as Solved.

**inoka** · 04-09-2018, 02:58 AM

Hi jindon ,
Since this request is not related with this thread, I thought you will not reply for me. Therefore, I posted new thread. If you can find solution for me, I'll close this thread as "solved".

**inoka** · 04-09-2018, 02:59 AM

Ok. I'll mark as "solved". Thank you so much.

**jindon** · 04-09-2018, 03:40 AM

Originally Posted by inoka

Hi jindon ,
Since this request is not related with this thread, I thought you will not reply for me. Therefore, I posted new thread. If you can find solution for me, I'll close this thread as "solved".

You are absolutely correct.

**inoka** · 04-09-2018, 03:59 AM

Hi jindon ,
Then , how can I get the solution to my problem. new thread has already closed.

**jindon** · 04-09-2018, 04:03 AM

I think you need ask MODERATOR.

**inoka** · 04-09-2018, 04:07 AM

Originally Posted by jindon

I think you need ask MODERATOR.

Moderator??
What is that?

**AliGW** · 04-09-2018, 04:11 AM

The new thread was closed because Jindon pointed straight back here for a solution. If that is not correct, then why the link back here? Jindon - please explain. If necessary I will reopen the thrad, but you will then need to remove the link and asnwer it in situ. This is getting rather confusing.

**jindon** · 04-09-2018, 04:13 AM

You can see Moderators of this Forum at the bottm.

https://www.excelforum.com/excel-pro...ng-vba-macros/

**AliGW** · 04-09-2018, 04:17 AM

Jindon - please answer my question.

**inoka** · 04-09-2018, 04:19 AM

Originally Posted by jindon

You can see Moderators of this Forum at the bottm.

https://www.excelforum.com/excel-pro...ng-vba-macros/

Hi jindon,

I am not able to understand what should I do. It's great, if I can get the solution for my new thread.

**jindon** · 04-09-2018, 04:20 AM

Link to show the code and I didn't need to write again.

Any problem??????

**inoka** · 04-09-2018, 04:31 AM

Link to show the code?? where it is? I was not able to find...

**jindon** · 04-09-2018, 04:33 AM

No, it is not to you...

why the link back here? Jindon - please explain.

**inoka** · 04-09-2018, 04:36 AM

Hi jindon ,
I am so confused. How can I find the solution to my problem? Do I need to add new thread?

**jindon** · 04-09-2018, 04:41 AM

Sorry, but I'm not in the position to solve this matter.

As I mentioned already, one way is to send a PM to a moderator.

**inoka** · 04-09-2018, 04:46 AM

Originally Posted by jindon

Sorry, but I'm not in the position to solve this matter.

As I mentioned already, one way is to send a PM to a moderator.

Ok. Thank you.

**inoka** · 04-09-2018, 04:48 AM

Hi jindon ,

I'll close this thread. you gave a great support for me.

**AliGW** · 04-09-2018, 05:12 AM

OK Inoka - so here's the thing: I closed the new thread you created because it wasn't needed. Jindon posted a link to point you back here, because it was not necessary for you to open another thread on the same (or very similar) issue. You have since opened yet another thread on the same issue which has now also been closed by another moderator.

You really MUST read this forum's rules before posting again, bescause you agreed to abide by them when you joined here. Here's a quick link to them: https://www.excelforum.com/forum-rul...rum-rules.html

If you continue to do your own thing and completely ignore our rules, you can expect to find yourself ultimately banned from posting altogether: it really is in your best interest to comply.

**inoka** · 04-09-2018, 05:18 AM

Extremely very sorry for it. I was so confused. That's why I posted new thread. Because, existing one is also closed. However, finally I was not able to find the solution for my data validation problem.

**jindon** · 04-09-2018, 05:18 AM

Originally Posted by AliGW

Jindon posted a link to point you back here, because it was not necessary for you to open another thread on the same (or very similar) issue.

I said I gave a link there only for the reference of the code I have written here, so I didn't need to write it again and continue THERE for the new question.
I don't think OP offend the rule.

**AliGW** · 04-09-2018, 05:21 AM

Then you have completely confused the issue by failing to explain that fully in the new thread. If the code was relevant to the new thread, then you should have posted the code in the new thread, not a link.

Inoka & Jindon - please continue in THIS thread with the similar issue.

**jindon** · 04-09-2018, 05:23 AM

What?

Is there such rule here???

**AliGW** · 04-09-2018, 05:26 AM

There doesn't need to be a rule, Jindon: it's common sense.

Inoka - we are all confused because you keep opening new threads that really aren't necessary.

This is the end of the matter. Please continue here with your issue if it still needs resolving.

**jindon** · 04-09-2018, 05:28 AM

Common sense....haha

**inoka** · 04-09-2018, 05:33 AM

Hi,
I need this to solve.
When copy and paste data from another sheet or workbook into the data validation column, I need to automatically clear all invalid data. and it should be remained only the valid data.
Please find the following attached excel sheet.

**inoka** · 04-09-2018, 12:00 PM

Hi jindon ,
Is there any solution for me.

**inoka** · 04-17-2018, 05:26 AM

Hi,
I didn't get any solution...

Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Re: Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Check Date Format Input into Excel Column with VBA code ("DD/MM/YYYY" format)

[SOLVED] Userform Dynamically Generated Combobox format value as date ("mm/dd/yyyy")

[SOLVED] Format a column as date format "yyyy-mm-dd"

Bookmarks

Bookmarks

Posting Permissions