user form text box date problems

I Have a user form with a no. of text boxes

textbox 3 to 5 the user is required to enter a date in the numeric format

2 day characters then 2 month characters and then the year

e.g.

10-09-2005 (ex1) or 15-09-2005 (ex2)

I Have the following code attached to a button on the user form to do this.

Worksheets("MainPage").range("C22").Value = Format(TextBox3.Text,_
"dd-mm-yyyy")
Worksheets("Data Entry").range("B2").Value = Format(TextBox4.Text,_
"dd-mm-yyyy")
Worksheets("Data Entry").range("B3").Value = Format(TextBox5.Text,_
"dd-mm-yyyy")

The problem is the when a date is used such as ex1 above when the date gets
to the worksheet the month and day part has been reversed.

Also when you look at hte cell on the work sheet it has been formatted as
09/10/2005. Which is not the instruction in the format command.

Any help you can provide would be appreciated.

Regards

Francis

Hi,

The Format statement applies to the building of a string, not to the format
of the cell.
Instead try to format the cell as a date:
Dim rg as range
set rg=Worksheets("MainPage").range("C22")
rg.Value = Format(TextBox3.Text, "dd-mm-yyyy") ' send string value to
the cell
rg.NumberFormat="dd-mm-yyyy" ' <--- formatting of cell as date

Would that work? There might be some issues in the mm-dd-yyy versus the
dd-mm-yyyy formats when passing dates from vb to excel, but not sure exactly
as i am using an english system with english excel. Let us know if any issue
though.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Francis Brown" wrote:

> I Have a user form with a no. of text boxes
>
> textbox 3 to 5 the user is required to enter a date in the numeric format
>
> 2 day characters then 2 month characters and then the year
>
> e.g.
>
> 10-09-2005 (ex1) or 15-09-2005 (ex2)
>
> I Have the following code attached to a button on the user form to do this.
>
> Worksheets("MainPage").range("C22").Value = Format(TextBox3.Text,_
> "dd-mm-yyyy")
> Worksheets("Data Entry").range("B2").Value = Format(TextBox4.Text,_
> "dd-mm-yyyy")
> Worksheets("Data Entry").range("B3").Value = Format(TextBox5.Text,_
> "dd-mm-yyyy")
>
> The problem is the when a date is used such as ex1 above when the date gets
> to the worksheet the month and day part has been reversed.
>
> Also when you look at hte cell on the work sheet it has been formatted as
> 09/10/2005. Which is not the instruction in the format command.
>
> Any help you can provide would be appreciated.
>
> Regards
>
> Francis
>
>

This dosent seem to have resolved the problem.

Excel insists on changing the format of the cells to:

dd/mm/yyyy

Also if the value I type into the text box for entry is

dd-mm-yyyy format but the dd value is twele or less the program transposes
then with the mm value which is corupting the data.

Any other sugestions.

Francis

"sebastienm" wrote:

> Hi,
>
> The Format statement applies to the building of a string, not to the format
> of the cell.
> Instead try to format the cell as a date:
> Dim rg as range
> set rg=Worksheets("MainPage").range("C22")
> rg.Value = Format(TextBox3.Text, "dd-mm-yyyy") ' send string value to
> the cell
> rg.NumberFormat="dd-mm-yyyy" ' <--- formatting of cell as date
>
> Would that work? There might be some issues in the mm-dd-yyy versus the
> dd-mm-yyyy formats when passing dates from vb to excel, but not sure exactly
> as i am using an english system with english excel. Let us know if any issue
> though.
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Francis Brown" wrote:
>
> > I Have a user form with a no. of text boxes
> >
> > textbox 3 to 5 the user is required to enter a date in the numeric format
> >
> > 2 day characters then 2 month characters and then the year
> >
> > e.g.
> >
> > 10-09-2005 (ex1) or 15-09-2005 (ex2)
> >
> > I Have the following code attached to a button on the user form to do this.
> >
> > Worksheets("MainPage").range("C22").Value = Format(TextBox3.Text,_
> > "dd-mm-yyyy")
> > Worksheets("Data Entry").range("B2").Value = Format(TextBox4.Text,_
> > "dd-mm-yyyy")
> > Worksheets("Data Entry").range("B3").Value = Format(TextBox5.Text,_
> > "dd-mm-yyyy")
> >
> > The problem is the when a date is used such as ex1 above when the date gets
> > to the worksheet the month and day part has been reversed.
> >
> > Also when you look at hte cell on the work sheet it has been formatted as
> > 09/10/2005. Which is not the instruction in the format command.
> >
> > Any help you can provide would be appreciated.
> >
> > Regards
> >
> > Francis
> >
> >

This would mean at some point the string is converted into English date.
Is the user entering as date as dd-mm-yyyy or ddmmyyyy. Since you said in
numeric format , i'll asssume ddmmyyyy from now on.

Try the follwing:

Dim rg as range
Dim nDay as Long, nMonth as Long, nYear as Long, d as Date

'Get Date entry
nDay= clng(Left(TextBox3.Text, 2)) '2 first chars converted to number
nMonth=clng(mid(TextBox3.Text, 3,2) '2 middle char conevrted to number
nYear=clng(right((TextBox3.Text, len(TextBox3.Text)-4) 'the rest to number
d=Dateserial(nYear, nMonth, nDay)

'Send to sheet
set rg=Worksheets("MainPage").range("C22")
rg.Value = d
rg.NumberFormat="dd-mm-yyyy"

Does that works?
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Francis Brown" wrote:

> This dosent seem to have resolved the problem.
>
> Excel insists on changing the format of the cells to:
>
> dd/mm/yyyy
>
> Also if the value I type into the text box for entry is
>
> dd-mm-yyyy format but the dd value is twele or less the program transposes
> then with the mm value which is corupting the data.
>
> Any other sugestions.
>
> Francis
>
> "sebastienm" wrote:
>
> > Hi,
> >
> > The Format statement applies to the building of a string, not to the format
> > of the cell.
> > Instead try to format the cell as a date:
> > Dim rg as range
> > set rg=Worksheets("MainPage").range("C22")
> > rg.Value = Format(TextBox3.Text, "dd-mm-yyyy") ' send string value to
> > the cell
> > rg.NumberFormat="dd-mm-yyyy" ' <--- formatting of cell as date
> >
> > Would that work? There might be some issues in the mm-dd-yyy versus the
> > dd-mm-yyyy formats when passing dates from vb to excel, but not sure exactly
> > as i am using an english system with english excel. Let us know if any issue
> > though.
> >
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "Francis Brown" wrote:
> >
> > > I Have a user form with a no. of text boxes
> > >
> > > textbox 3 to 5 the user is required to enter a date in the numeric format
> > >
> > > 2 day characters then 2 month characters and then the year
> > >
> > > e.g.
> > >
> > > 10-09-2005 (ex1) or 15-09-2005 (ex2)
> > >
> > > I Have the following code attached to a button on the user form to do this.
> > >
> > > Worksheets("MainPage").range("C22").Value = Format(TextBox3.Text,_
> > > "dd-mm-yyyy")
> > > Worksheets("Data Entry").range("B2").Value = Format(TextBox4.Text,_
> > > "dd-mm-yyyy")
> > > Worksheets("Data Entry").range("B3").Value = Format(TextBox5.Text,_
> > > "dd-mm-yyyy")
> > >
> > > The problem is the when a date is used such as ex1 above when the date gets
> > > to the worksheet the month and day part has been reversed.
> > >
> > > Also when you look at hte cell on the work sheet it has been formatted as
> > > 09/10/2005. Which is not the instruction in the format command.
> > >
> > > Any help you can provide would be appreciated.
> > >
> > > Regards
> > >
> > > Francis
> > >
> > >

The Code worked with slight adjustment as i'm using the dd-mm-yyyy format.

Also. The right function did not work as programed.

took the len part out and left simply as 4. VBA help states function counts
from right for this function.

Anyway resolved now and thanks for help. Just shows how silly excel is that
you need all this code just to make a textbox copy a date to a cell.

Regards.

Francis

"sebastienm" wrote:

> This would mean at some point the string is converted into English date.
> Is the user entering as date as dd-mm-yyyy or ddmmyyyy. Since you said in
> numeric format , i'll asssume ddmmyyyy from now on.
>
> Try the follwing:
>
> Dim rg as range
> Dim nDay as Long, nMonth as Long, nYear as Long, d as Date
>
> 'Get Date entry
> nDay= clng(Left(TextBox3.Text, 2)) '2 first chars converted to number
> nMonth=clng(mid(TextBox3.Text, 3,2) '2 middle char conevrted to number
> nYear=clng(right((TextBox3.Text, len(TextBox3.Text)-4) 'the rest to number
> d=Dateserial(nYear, nMonth, nDay)
>
> 'Send to sheet
> set rg=Worksheets("MainPage").range("C22")
> rg.Value = d
> rg.NumberFormat="dd-mm-yyyy"
>
> Does that works?
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Francis Brown" wrote:
>
> > This dosent seem to have resolved the problem.
> >
> > Excel insists on changing the format of the cells to:
> >
> > dd/mm/yyyy
> >
> > Also if the value I type into the text box for entry is
> >
> > dd-mm-yyyy format but the dd value is twele or less the program transposes
> > then with the mm value which is corupting the data.
> >
> > Any other sugestions.
> >
> > Francis
> >
> > "sebastienm" wrote:
> >
> > > Hi,
> > >
> > > The Format statement applies to the building of a string, not to the format
> > > of the cell.
> > > Instead try to format the cell as a date:
> > > Dim rg as range
> > > set rg=Worksheets("MainPage").range("C22")
> > > rg.Value = Format(TextBox3.Text, "dd-mm-yyyy") ' send string value to
> > > the cell
> > > rg.NumberFormat="dd-mm-yyyy" ' <--- formatting of cell as date
> > >
> > > Would that work? There might be some issues in the mm-dd-yyy versus the
> > > dd-mm-yyyy formats when passing dates from vb to excel, but not sure exactly
> > > as i am using an english system with english excel. Let us know if any issue
> > > though.
> > >
> > > --
> > > Regards,
> > > Sébastien
> > > <http://www.ondemandanalysis.com>
> > >
> > >
> > > "Francis Brown" wrote:
> > >
> > > > I Have a user form with a no. of text boxes
> > > >
> > > > textbox 3 to 5 the user is required to enter a date in the numeric format
> > > >
> > > > 2 day characters then 2 month characters and then the year
> > > >
> > > > e.g.
> > > >
> > > > 10-09-2005 (ex1) or 15-09-2005 (ex2)
> > > >
> > > > I Have the following code attached to a button on the user form to do this.
> > > >
> > > > Worksheets("MainPage").range("C22").Value = Format(TextBox3.Text,_
> > > > "dd-mm-yyyy")
> > > > Worksheets("Data Entry").range("B2").Value = Format(TextBox4.Text,_
> > > > "dd-mm-yyyy")
> > > > Worksheets("Data Entry").range("B3").Value = Format(TextBox5.Text,_
> > > > "dd-mm-yyyy")
> > > >
> > > > The problem is the when a date is used such as ex1 above when the date gets
> > > > to the worksheet the month and day part has been reversed.
> > > >
> > > > Also when you look at hte cell on the work sheet it has been formatted as
> > > > 09/10/2005. Which is not the instruction in the format command.
> > > >
> > > > Any help you can provide would be appreciated.
> > > >
> > > > Regards
> > > >
> > > > Francis
> > > >
> > > >

But you don't have to use all that code.

with Worksheets("MainPage").range("C22")
.Value = cDate(Textbox3.Text)
.NumberFormat="dd-mm-yyyy"
End with

--
Regards,
Tom Ogilvy


"Francis Brown" <FrancisBrown@discussions.microsoft.com> wrote in message
news:B479F3D4-C643-4DB5-A1AC-7FFF3495C320@microsoft.com...
> The Code worked with slight adjustment as i'm using the dd-mm-yyyy format.
>
> Also. The right function did not work as programed.
>
> took the len part out and left simply as 4. VBA help states function
counts
> from right for this function.
>
> Anyway resolved now and thanks for help. Just shows how silly excel is
that
> you need all this code just to make a textbox copy a date to a cell.
>
> Regards.
>
> Francis
>
> "sebastienm" wrote:
>
> > This would mean at some point the string is converted into English date.
> > Is the user entering as date as dd-mm-yyyy or ddmmyyyy. Since you said
in
> > numeric format , i'll asssume ddmmyyyy from now on.
> >
> > Try the follwing:
> >
> > Dim rg as range
> > Dim nDay as Long, nMonth as Long, nYear as Long, d as Date
> >
> > 'Get Date entry
> > nDay= clng(Left(TextBox3.Text, 2)) '2 first chars converted to
number
> > nMonth=clng(mid(TextBox3.Text, 3,2) '2 middle char conevrted to
number
> > nYear=clng(right((TextBox3.Text, len(TextBox3.Text)-4) 'the rest to
number
> > d=Dateserial(nYear, nMonth, nDay)
> >
> > 'Send to sheet
> > set rg=Worksheets("MainPage").range("C22")
> > rg.Value = d
> > rg.NumberFormat="dd-mm-yyyy"
> >
> > Does that works?
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "Francis Brown" wrote:
> >
> > > This dosent seem to have resolved the problem.
> > >
> > > Excel insists on changing the format of the cells to:
> > >
> > > dd/mm/yyyy
> > >
> > > Also if the value I type into the text box for entry is
> > >
> > > dd-mm-yyyy format but the dd value is twele or less the program
transposes
> > > then with the mm value which is corupting the data.
> > >
> > > Any other sugestions.
> > >
> > > Francis
> > >
> > > "sebastienm" wrote:
> > >
> > > > Hi,
> > > >
> > > > The Format statement applies to the building of a string, not to the
format
> > > > of the cell.
> > > > Instead try to format the cell as a date:
> > > > Dim rg as range
> > > > set rg=Worksheets("MainPage").range("C22")
> > > > rg.Value = Format(TextBox3.Text, "dd-mm-yyyy") ' send string
value to
> > > > the cell
> > > > rg.NumberFormat="dd-mm-yyyy" ' <--- formatting of cell as
date
> > > >
> > > > Would that work? There might be some issues in the mm-dd-yyy versus
the
> > > > dd-mm-yyyy formats when passing dates from vb to excel, but not sure
exactly
> > > > as i am using an english system with english excel. Let us know if
any issue
> > > > though.
> > > >
> > > > --
> > > > Regards,
> > > > Sébastien
> > > > <http://www.ondemandanalysis.com>
> > > >
> > > >
> > > > "Francis Brown" wrote:
> > > >
> > > > > I Have a user form with a no. of text boxes
> > > > >
> > > > > textbox 3 to 5 the user is required to enter a date in the numeric
format
> > > > >
> > > > > 2 day characters then 2 month characters and then the year
> > > > >
> > > > > e.g.
> > > > >
> > > > > 10-09-2005 (ex1) or 15-09-2005 (ex2)
> > > > >
> > > > > I Have the following code attached to a button on the user form to
do this.
> > > > >
> > > > > Worksheets("MainPage").range("C22").Value = Format(TextBox3.Text,_
> > > > > "dd-mm-yyyy")
> > > > > Worksheets("Data Entry").range("B2").Value =
Format(TextBox4.Text,_
> > > > > "dd-mm-yyyy")
> > > > > Worksheets("Data Entry").range("B3").Value =
Format(TextBox5.Text,_
> > > > > "dd-mm-yyyy")
> > > > >
> > > > > The problem is the when a date is used such as ex1 above when the
date gets
> > > > > to the worksheet the month and day part has been reversed.
> > > > >
> > > > > Also when you look at hte cell on the work sheet it has been
formatted as
> > > > > 09/10/2005. Which is not the instruction in the format command.
> > > > >
> > > > > Any help you can provide would be appreciated.
> > > > >
> > > > > Regards
> > > > >
> > > > > Francis
> > > > >
> > > > >