Prev: Please help with Custom Views
Next: How to redistribute microsoft.office.interop.excel?
From: J.R. on 11 May 2010 19:15
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.

From: GS on 11 May 2010 22:50
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


From: J.R. on 14 May 2010 14:27
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.


"J.R." wrote:

> 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.
>
From: Gord Dibben on 14 May 2010 16:53
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

On Fri, 14 May 2010 11:27:01 -0700, J.R. <JR(a)discussions.microsoft.com>
wrote:

>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.
>
>
>"J.R." wrote:
>
>> 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.
>>

From: J.R. on 15 May 2010 14:22
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" wrote:

> 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
>
> On Fri, 14 May 2010 11:27:01 -0700, J.R. <JR(a)discussions.microsoft.com>
> wrote:
>
> >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.
> >
> >
> >"J.R." wrote:
> >
> >> 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.
> >>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Please help with Custom Views
Next: How to redistribute microsoft.office.interop.excel?