Date format changes when inputed from user form
 

Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy. I
can get it to enter in the correct format if I use a calendar to add the
date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right but
nothing Ive tried works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

I would use something different than a textbox.

Either a calendar control...

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 textboxes/comboboxes/spinners/scrollbars.
One for the month, one for the day and one for the year.

Lynz wrote:

Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy. I
can get it to enter in the correct format if I use a calendar to add the
date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right but
nothing Ive tried works.
Thank you for any assistance
L

--

Dave Peterson

Date format changes when inputed from user form
 

Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and the
data is entered onto my spread sheet the date enters as mm/dd/yy. I can
get it to enter in the correct format if I use a calendar to add the date
but I dont want to do this If I can help it. Is there an easy solution to
this problem. I have spent hours trying to get it right but nothing Ive
tried works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and the
data is entered onto my spread sheet the date enters as mm/dd/yy. I can
get it to enter in the correct format if I use a calendar to add the date
but I dont want to do this If I can help it. Is there an easy solution to
this problem. I have spent hours trying to get it right but nothing Ive
tried works.
Thank you for any assistance
L


Thank you for the quick reply, I have tried Formating as date, text and
custom and if I put 10/12/08 in my text box it still comes out as
12/10/08 on my spread sheet.
L

Date format changes when inputed from user form
 

Dave Peterson wrote:
I would use something different than a textbox.

Either a calendar control...

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 textboxes/comboboxes/spinners/scrollbars.
One for the month, one for the day and one for the year.

Lynz wrote:
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy. I
can get it to enter in the correct format if I use a calendar to add the
date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right but
nothing Ive tried works.
Thank you for any assistance
L

Thanks, but I was trying to keep it simple, maybe ill just add "todays
date" and modify if necessary. Sometimes it will be todays date and
other times it will be a previous days date depending on how up to date
my data entry is.
L

Date format changes when inputed from user form
 

That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting in. To be
unambiguous, display the data (at least for the time being) as something
like dd mmm yyyy. If you are seeing 12 Oct 2008 when you wanted 10 Dec
2008, the problem isn't in your Excel display format, but in your Windows
Regional Settings, which you can get at and change through the Control
Panel.
--
David Biddulph

"Lynz" wrote in message
...
Thank you for the quick reply, I have tried Formating as date, text and
custom and if I put 10/12/08 in my text box it still comes out as 12/10/08
on my spread sheet.
L

David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy. I
can get it to enter in the correct format if I use a calendar to add the
date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right but
nothing Ive tried works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

The problem is with text that looks like a date using the user's windows
regional settings short date order (mdy or dmy or ...).

If your program sees
1/2/3
in a cell, excel will use whatever shortdate format order that the user is using
to parse the entry.

If it's mdy, you'll end up with Jan 2, 2003
If it's dmy, you'll end up with Feb 1, 2003
If it's ymd, you'll end up with Feb 3, 2001

And if the text box looks like:
31/12/2008
and the user is using mdy order, then you'll end up with a string--it won't even
be a date.

I guess if you always know that the order in the textbox is dmy, you could parse
the entry into 3 pieces and create a real date from that.

But I don't know how you would know what the user meant when an ambiguous string
is entered.



Lynz wrote:

David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and the
data is entered onto my spread sheet the date enters as mm/dd/yy. I can
get it to enter in the correct format if I use a calendar to add the date
but I dont want to do this If I can help it. Is there an easy solution to
this problem. I have spent hours trying to get it right but nothing Ive
tried works.
Thank you for any assistance
L


Thank you for the quick reply, I have tried Formating as date, text and
custom and if I put 10/12/08 in my text box it still comes out as
12/10/08 on my spread sheet.
L

--

Dave Peterson

Date format changes when inputed from user form
 

David Biddulph wrote:
That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting in. To be
unambiguous, display the data (at least for the time being) as something
like dd mmm yyyy. If you are seeing 12 Oct 2008 when you wanted 10 Dec
2008, the problem isn't in your Excel display format, but in your Windows
Regional Settings, which you can get at and change through the Control
Panel.
--
David Biddulph

I have been into the settings in control panel and short date is
dd/MM/yy , long date is ddd/mmm/yyyy. IT was a bit hard to tell what it
was set as as today is 12/12/08 as luck would have it. Anyway tried my
form again and 10/12/08 came out as 12/10/08, looks like I will just
have to use a calendar which I have tried before on another form and it
inputs the correct date. I only use dd/mm/yy as it is quick and easy to
type.
Thank you for all the replies of help, much appreciated.
Merry xmas L



a

"Lynz" wrote in message
...
Thank you for the quick reply, I have tried Formating as date, text and
custom and if I put 10/12/08 in my text box it still comes out as 12/10/08
on my spread sheet.
L

David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy. I
can get it to enter in the correct format if I use a calendar to add the
date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right but
nothing Ive tried works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

If I were you I would recheck your settings carefully, both your Regional
Settings and your Excel cell format.
You didn't respond to my suggestion that you should format the cell
temporarily as something like dd mmm yyyy.
If your Regional Settings are really dd/MM/yy, then if you input 10/12/08
you should NOT be seeing 12 Oct 2008; you should be seeing 10 Dec 2008. Are
you sure that you are seeing 12 Oct 2008 when you input 10/12/08?
If you see 12 Oct 2008 then it is your regional settings that are wrong. If
you see 10 Dec 2008, then to see it as 10/12/08 you just need to set your
cell format to dd/mm/yy.
--
David Biddulph

"Lynz" wrote in message
...
David Biddulph wrote:
That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting in. To
be unambiguous, display the data (at least for the time being) as
something like dd mmm yyyy. If you are seeing 12 Oct 2008 when you
wanted 10 Dec 2008, the problem isn't in your Excel display format, but
in your Windows Regional Settings, which you can get at and change
through the Control Panel.
--
David Biddulph

I have been into the settings in control panel and short date is dd/MM/yy
, long date is ddd/mmm/yyyy. IT was a bit hard to tell what it was set as
as today is 12/12/08 as luck would have it. Anyway tried my form again and
10/12/08 came out as 12/10/08, looks like I will just have to use a
calendar which I have tried before on another form and it inputs the
correct date. I only use dd/mm/yy as it is quick and easy to type.
Thank you for all the replies of help, much appreciated.
Merry xmas L



a

"Lynz" wrote in message
...
Thank you for the quick reply, I have tried Formating as date, text and
custom and if I put 10/12/08 in my text box it still comes out as
12/10/08 on my spread sheet.
L

David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard
option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy.
I can get it to enter in the correct format if I use a calendar to add
the date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right
but nothing Ive tried works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

David Biddulph wrote:
If I were you I would recheck your settings carefully, both your Regional
Settings and your Excel cell format.
You didn't respond to my suggestion that you should format the cell
temporarily as something like dd mmm yyyy.
If your Regional Settings are really dd/MM/yy, then if you input 10/12/08
you should NOT be seeing 12 Oct 2008; you should be seeing 10 Dec 2008. Are
you sure that you are seeing 12 Oct 2008 when you input 10/12/08?
If you see 12 Oct 2008 then it is your regional settings that are wrong. If
you see 10 Dec 2008, then to see it as 10/12/08 you just need to set your
cell format to dd/mm/yy.
--
David Biddulph
Hi David thanks again for replying
I set the format for the cells to dd/mmm/yyyy and entered 10/12/08 in my
user form. This entered the date as 12/Oct/2008. If I enter the same
thing directly into the cell it comes out as 10/Dec/2008 which is
correct, it is just changing when entered via the user form.. very
confusing. By the way, my macro inserts a new row at the top of my data
after adding my data so that it is always on the top. Would this change
the formatting?
L


"Lynz" wrote in message
...
David Biddulph wrote:
That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting in. To
be unambiguous, display the data (at least for the time being) as
something like dd mmm yyyy. If you are seeing 12 Oct 2008 when you
wanted 10 Dec 2008, the problem isn't in your Excel display format, but
in your Windows Regional Settings, which you can get at and change
through the Control Panel.
--
David Biddulph
I have been into the settings in control panel and short date is dd/MM/yy
, long date is ddd/mmm/yyyy. IT was a bit hard to tell what it was set as
as today is 12/12/08 as luck would have it. Anyway tried my form again and
10/12/08 came out as 12/10/08, looks like I will just have to use a
calendar which I have tried before on another form and it inputs the
correct date. I only use dd/mm/yy as it is quick and easy to type.
Thank you for all the replies of help, much appreciated.
Merry xmas L



a
"Lynz" wrote in message
...
Thank you for the quick reply, I have tried Formating as date, text and
custom and if I put 10/12/08 in my text box it still comes out as
12/10/08 on my spread sheet.
L
David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard
option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I type the
date in format dd/mm/yy. However when I click on the enter button and
the data is entered onto my spread sheet the date enters as mm/dd/yy.
I can get it to enter in the correct format if I use a calendar to add
the date but I dont want to do this If I can help it. Is there an easy
solution to this problem. I have spent hours trying to get it right
but nothing Ive tried works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

Lynz wrote:
David Biddulph wrote:
If I were you I would recheck your settings carefully, both your
Regional Settings and your Excel cell format.
You didn't respond to my suggestion that you should format the cell
temporarily as something like dd mmm yyyy.
If your Regional Settings are really dd/MM/yy, then if you input
10/12/08 you should NOT be seeing 12 Oct 2008; you should be seeing 10
Dec 2008. Are you sure that you are seeing 12 Oct 2008 when you input
10/12/08?
If you see 12 Oct 2008 then it is your regional settings that are
wrong. If you see 10 Dec 2008, then to see it as 10/12/08 you just
need to set your cell format to dd/mm/yy.
--
David Biddulph
Hi David thanks again for replying
I set the format for the cells to dd/mmm/yyyy and entered 10/12/08 in my
user form. This entered the date as 12/Oct/2008. If I enter the same
thing directly into the cell it comes out as 10/Dec/2008 which is
correct, it is just changing when entered via the user form.. very
confusing. By the way, my macro inserts a new row at the top of my data
after adding my data so that it is always on the top. Would this change
the formatting?
L

Just replying to myself here but this is the code that adds my data.
The dates Im having trouble with are in text box 21. Do I need to add
some formatting here somewhere maybe??
L
Private Sub CommandButton8_Click()
ActiveWorkbook.Sheets("Bulling").Activate
Range("A3").Value = Format(Now, "dd mmm yyyy")
Range("B3").Value = ComboBox6.Value
Range("C3").Value = TextBox21.Value
Range("E3").Value = TextBox22.Value
Range("G3").Value = ComboBox5.Value
Range("H3").Value = TextBox23.Value
Range("A3:Z3").Select
Selection.EntireRow.Insert
Range("A3").Select
ComboBox6.Value = "Cow"
TextBox21.Value = "Bulling 1"
TextBox22.Value = "Bulling 2"
TextBox23.Value = "Comments"
ComboBox5.Value = "Red Devon 488"
ComboBox6.SetFocus


End Sub

"Lynz" wrote in message
...
David Biddulph wrote:
That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting
in. To be unambiguous, display the data (at least for the time
being) as something like dd mmm yyyy. If you are seeing 12 Oct 2008
when you wanted 10 Dec 2008, the problem isn't in your Excel display
format, but in your Windows Regional Settings, which you can get at
and change through the Control Panel.
--
David Biddulph
I have been into the settings in control panel and short date is
dd/MM/yy , long date is ddd/mmm/yyyy. IT was a bit hard to tell what
it was set as as today is 12/12/08 as luck would have it. Anyway
tried my form again and 10/12/08 came out as 12/10/08, looks like I
will just have to use a calendar which I have tried before on another
form and it inputs the correct date. I only use dd/mm/yy as it is
quick and easy to type.
Thank you for all the replies of help, much appreciated.
Merry xmas L



a
"Lynz" wrote in message
...
Thank you for the quick reply, I have tried Formating as date, text
and custom and if I put 10/12/08 in my text box it still comes out
as 12/10/08 on my spread sheet.
L
David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard
option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I
type the date in format dd/mm/yy. However when I click on the
enter button and the data is entered onto my spread sheet the
date enters as mm/dd/yy. I can get it to enter in the correct
format if I use a calendar to add the date but I dont want to do
this If I can help it. Is there an easy solution to this problem.
I have spent hours trying to get it right but nothing Ive tried
works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

Something to try:

Option Explicit
Private Sub CommandButton1_Click()
With ActiveSheet.Range("A1")
.NumberFormat = "mmmm dd, yyyy"
.Value = CDate(Me.TextBox1.Value)
End With
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Value = "11/12/2008"
End Sub


But I still don't know how you as the developer will know what the user meant
when he/she typed 11/12/2008.



Lynz wrote:

Lynz wrote:
David Biddulph wrote:
If I were you I would recheck your settings carefully, both your
Regional Settings and your Excel cell format.
You didn't respond to my suggestion that you should format the cell
temporarily as something like dd mmm yyyy.
If your Regional Settings are really dd/MM/yy, then if you input
10/12/08 you should NOT be seeing 12 Oct 2008; you should be seeing 10
Dec 2008. Are you sure that you are seeing 12 Oct 2008 when you input
10/12/08?
If you see 12 Oct 2008 then it is your regional settings that are
wrong. If you see 10 Dec 2008, then to see it as 10/12/08 you just
need to set your cell format to dd/mm/yy.
--
David Biddulph
Hi David thanks again for replying
I set the format for the cells to dd/mmm/yyyy and entered 10/12/08 in my
user form. This entered the date as 12/Oct/2008. If I enter the same
thing directly into the cell it comes out as 10/Dec/2008 which is
correct, it is just changing when entered via the user form.. very
confusing. By the way, my macro inserts a new row at the top of my data
after adding my data so that it is always on the top. Would this change
the formatting?
L

Just replying to myself here but this is the code that adds my data.
The dates Im having trouble with are in text box 21. Do I need to add
some formatting here somewhere maybe??
L
Private Sub CommandButton8_Click()
ActiveWorkbook.Sheets("Bulling").Activate
Range("A3").Value = Format(Now, "dd mmm yyyy")
Range("B3").Value = ComboBox6.Value
Range("C3").Value = TextBox21.Value
Range("E3").Value = TextBox22.Value
Range("G3").Value = ComboBox5.Value
Range("H3").Value = TextBox23.Value
Range("A3:Z3").Select
Selection.EntireRow.Insert
Range("A3").Select
ComboBox6.Value = "Cow"
TextBox21.Value = "Bulling 1"
TextBox22.Value = "Bulling 2"
TextBox23.Value = "Comments"
ComboBox5.Value = "Red Devon 488"
ComboBox6.SetFocus

End Sub

"Lynz" wrote in message
...
David Biddulph wrote:
That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting
in. To be unambiguous, display the data (at least for the time
being) as something like dd mmm yyyy. If you are seeing 12 Oct 2008
when you wanted 10 Dec 2008, the problem isn't in your Excel display
format, but in your Windows Regional Settings, which you can get at
and change through the Control Panel.
--
David Biddulph
I have been into the settings in control panel and short date is
dd/MM/yy , long date is ddd/mmm/yyyy. IT was a bit hard to tell what
it was set as as today is 12/12/08 as luck would have it. Anyway
tried my form again and 10/12/08 came out as 12/10/08, looks like I
will just have to use a calendar which I have tried before on another
form and it inputs the correct date. I only use dd/mm/yy as it is
quick and easy to type.
Thank you for all the replies of help, much appreciated.
Merry xmas L



a
"Lynz" wrote in message
...
Thank you for the quick reply, I have tried Formating as date, text
and custom and if I put 10/12/08 in my text box it still comes out
as 12/10/08 on my spread sheet.
L
David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard
option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I
type the date in format dd/mm/yy. However when I click on the
enter button and the data is entered onto my spread sheet the
date enters as mm/dd/yy. I can get it to enter in the correct
format if I use a calendar to add the date but I dont want to do
this If I can help it. Is there an easy solution to this problem.
I have spent hours trying to get it right but nothing Ive tried
works.
Thank you for any assistance
L


--

Dave Peterson

Date format changes when inputed from user form
 

Dave Peterson wrote:
Something to try:

Option Explicit
Private Sub CommandButton1_Click()
With ActiveSheet.Range("A1")
.NumberFormat = "mmmm dd, yyyy"
.Value = CDate(Me.TextBox1.Value)
End With
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Value = "11/12/2008"
End Sub


But I still don't know how you as the developer will know what the user meant
when he/she typed 11/12/2008.


Thanks Dave P and others, Ill see if that will help, havent tried it yet.
ps Im not a developer, Im a "Farmer", but I love working with excell. I
am the only person inputing the data and as I live in NZ I always use
dd/mm/yy. Maybe I need to change my date entering habits and go to
dd/mmm/yy or something instead. I am using the form to keep a track of
when my cows calve, when they come back on heat, when to check them
again in 21 days time,the date they were served,and when the expected
calving date will be next year etc. I hope I am not wasting your time.
L


Lynz wrote:
Lynz wrote:
David Biddulph wrote:
If I were you I would recheck your settings carefully, both your
Regional Settings and your Excel cell format.
You didn't respond to my suggestion that you should format the cell
temporarily as something like dd mmm yyyy.
If your Regional Settings are really dd/MM/yy, then if you input
10/12/08 you should NOT be seeing 12 Oct 2008; you should be seeing 10
Dec 2008. Are you sure that you are seeing 12 Oct 2008 when you input
10/12/08?
If you see 12 Oct 2008 then it is your regional settings that are
wrong. If you see 10 Dec 2008, then to see it as 10/12/08 you just
need to set your cell format to dd/mm/yy.
--
David Biddulph
Hi David thanks again for replying
I set the format for the cells to dd/mmm/yyyy and entered 10/12/08 in my
user form. This entered the date as 12/Oct/2008. If I enter the same
thing directly into the cell it comes out as 10/Dec/2008 which is
correct, it is just changing when entered via the user form.. very
confusing. By the way, my macro inserts a new row at the top of my data
after adding my data so that it is always on the top. Would this change
the formatting?
L
Just replying to myself here but this is the code that adds my data.
The dates Im having trouble with are in text box 21. Do I need to add
some formatting here somewhere maybe??
L
Private Sub CommandButton8_Click()
ActiveWorkbook.Sheets("Bulling").Activate
Range("A3").Value = Format(Now, "dd mmm yyyy")
Range("B3").Value = ComboBox6.Value
Range("C3").Value = TextBox21.Value
Range("E3").Value = TextBox22.Value
Range("G3").Value = ComboBox5.Value
Range("H3").Value = TextBox23.Value
Range("A3:Z3").Select
Selection.EntireRow.Insert
Range("A3").Select
ComboBox6.Value = "Cow"
TextBox21.Value = "Bulling 1"
TextBox22.Value = "Bulling 2"
TextBox23.Value = "Comments"
ComboBox5.Value = "Red Devon 488"
ComboBox6.SetFocus

End Sub

"Lynz" wrote in message
...
David Biddulph wrote:
That sounds as if it isn't a problem with the formatting of the date
display, but with the interpretation of the data you are putting
in. To be unambiguous, display the data (at least for the time
being) as something like dd mmm yyyy. If you are seeing 12 Oct 2008
when you wanted 10 Dec 2008, the problem isn't in your Excel display
format, but in your Windows Regional Settings, which you can get at
and change through the Control Panel.
--
David Biddulph
I have been into the settings in control panel and short date is
dd/MM/yy , long date is ddd/mmm/yyyy. IT was a bit hard to tell what
it was set as as today is 12/12/08 as luck would have it. Anyway
tried my form again and 10/12/08 came out as 12/10/08, looks like I
will just have to use a calendar which I have tried before on another
form and it inputs the correct date. I only use dd/mm/yy as it is
quick and easy to type.
Thank you for all the replies of help, much appreciated.
Merry xmas L



a
"Lynz" wrote in message
...
Thank you for the quick reply, I have tried Formating as date, text
and custom and if I put 10/12/08 in my text box it still comes out
as 12/10/08 on my spread sheet.
L
David Biddulph wrote:
Format/ Cells/ Number/ Date and choose an appropriate date format
or
Format/ Cells/ Number/ Custom if you want to use a less standard
option.
--
David Biddulph

"Lynz" wrote in message
...
Hi, I have a user form which contains text boxes into which I
type the date in format dd/mm/yy. However when I click on the
enter button and the data is entered onto my spread sheet the
date enters as mm/dd/yy. I can get it to enter in the correct
format if I use a calendar to add the date but I dont want to do
this If I can help it. Is there an easy solution to this problem.
I have spent hours trying to get it right but nothing Ive tried
works.
Thank you for any assistance
L

Date format changes when inputed from user form
 

Hi, Ive got it to work and enter the correct date at long last.

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21.Text = Format(TextBox21.Text, "dd/mmm/yy")

End Sub

Is it ok to do this? seems to work. Thanks for all your assistance.
Cheers L

Date format changes when inputed from user form
 

I would have used cdate().

Lynz wrote:

Hi, Ive got it to work and enter the correct date at long last.

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21.Text = Format(TextBox21.Text, "dd/mmm/yy")

End Sub

Is it ok to do this? seems to work. Thanks for all your assistance.
Cheers L

--

Dave Peterson

Date format changes when inputed from user form
 

ps.

But I bet any unambiguous date will work ok.

Lynz wrote:

Hi, Ive got it to work and enter the correct date at long last.

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21.Text = Format(TextBox21.Text, "dd/mmm/yy")

End Sub

Is it ok to do this? seems to work. Thanks for all your assistance.
Cheers L

--

Dave Peterson

Date format changes when inputed from user form
 

Dave Peterson wrote:
I would have used cdate().

Thanks Ill look that up.

Lynz wrote:
Hi, Ive got it to work and enter the correct date at long last.

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21.Text = Format(TextBox21.Text, "dd/mmm/yy")

End Sub

Is it ok to do this? seems to work. Thanks for all your assistance.
Cheers L