Data Validation AutoComplete Help

[J.R.]

Situation: In the “View Code†section of the sheet tab I have entered the
ComboBox AutoComplete code, which functions perfectly until I get to a
ComboBox containing the Date (dd/mm/yyyy).

Problem: When I begin to type the date it is automatically converted to a
whole number.

Example: 1/1/2010 converts to 40179

Question: Is there code that can be added to the “View Code†section of the
sheet tab that will prevent this conversion?

Source of code used:

http://www.contextures.com/xlDataVal11.html

Thank you in advance.

 

[GS]

J.R. formulated the question :

Situation: In the “View Code†section of the sheet tab I have entered the
ComboBox AutoComplete code, which functions perfectly until I get to a
ComboBox containing the Date (dd/mm/yyyy).

Problem: When I begin to type the date it is automatically converted to a
whole number.

Example: 1/1/2010 converts to 40179

Question: Is there code that can be added to the “View Code†section of the
sheet tab that will prevent this conversion?

Source of code used:

http://www.contextures.com/xlDataVal11.html

Thank you in advance.

Using VBA function: CDate(40179) returns 1/1/2010

Garry

 

[J.R.]

Hi GS, thank you for the recommendation. I unfortunately was unable to
successfully integrate VBA function: CDate into my code. After doing a fair
amount of research, and trying upwards of 50 different variations of the use
of CDate, it would appear that my intermediate level of VBA code writing is
not enough to solve my problem.

I’m not sure if I should embed CDate within one of the existing sheet tab
“View Code†Sub Routines, should I write and additional Sub Routine, or
should I start from scratch. And, I’m not sure any of the 50 or so variations
were ever correct in the first place.

In my situation I am dealing with 20+ cells on a single worksheet that
contain the date (format: dd/mm/yyyy), each independent of one another.

I thought that I had stumbled on someone’s solution for this some time back,
but I am unable to relocate that source.

Any additional help would be greatly appreciated.

Thanks again for your help.

 

[Gord Dibben]

Do you have to calculate with the dates?

If not, make them Text in the source list by preceding with an apostrophe.

I tested with Debra's example workbook and worked out fine.


Gord Dibben MS Excel MVP

 

[J.R.]

Hey Gord,

Your suggestion worked perfectly. As for the use in formulas, I believe I
can set up the MATCH() function to work around the formatting/calculation
issue.

Due to my hectic schedule this weekend, I my not be able to experiment with
this until next week, but I will post my findings when I am done.

Thank you for your help.

 

[Gord Dibben]

Yes, one extra step and you can convert to real dates in another cell if
need be.

Good luck, Gord