Seemingly changing date format?
 

Hi I have a sheet that I update using a form with the code below. The
problem I am having is that it seems to randomly change the date format
on the sheet from dd/mm/yyyy to mm/dd/yyyy. It seems to be totally
random. I have checked the cell formats and they seem to be all set to
dd/mm/yyyy. I have checked the system settings and they seem good too.
I can enter a couple of dates correctly then it will fail on me, if I
ignore it & continue it will suddenly do a few more dates as I wish and
then fail again. The dates on the form looks ok each time though.

Any suggestions out there.

I would really appreciatte any help.


Thanks.


Colin

Private Sub CommandButton8_Click()
If txtdate.Value = "" Then Exit Sub
If txttrain.Value = "" Then Exit Sub
ActiveSheet.Unprotect Password:="********"

Range("A14").Select
Do

If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txttrain.Value
ActiveCell.Offset(0, 1) = txtdate.Value
ActiveCell.Offset(0, 2) = txtresult.Value
ActiveCell.Offset(0, 3) = txtrefresh.Value
ActiveCell.Offset(0, 4) = txttrainer.Value



ActiveSheet.Protect Password:="********"
ActiveWorkbook.Save

Unload Me

frmdataentry.Show
End Sub


--
kiwicolin
------------------------------------------------------------------------
kiwicolin's Profile: http://www.excelforum.com/member.php...o&userid=19861
View this thread: http://www.excelforum.com/showthread...hreadid=473116

The value in txtdate is a string.

When you plop it into the worksheet excel takes over. It does what it wants.
If it sees it as a date, xl will treat it as a date. If xl doesn't see a date,
then it'll be treated as a string.

You might want to remove any ambiguity (and make validation a bit easier) by
using a calendar control on your userform.

Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm

kiwicolin wrote:

Hi I have a sheet that I update using a form with the code below. The
problem I am having is that it seems to randomly change the date format
on the sheet from dd/mm/yyyy to mm/dd/yyyy. It seems to be totally
random. I have checked the cell formats and they seem to be all set to
dd/mm/yyyy. I have checked the system settings and they seem good too.
I can enter a couple of dates correctly then it will fail on me, if I
ignore it & continue it will suddenly do a few more dates as I wish and
then fail again. The dates on the form looks ok each time though.

Any suggestions out there.

I would really appreciatte any help.

Thanks.

Colin

Private Sub CommandButton8_Click()
If txtdate.Value = "" Then Exit Sub
If txttrain.Value = "" Then Exit Sub
ActiveSheet.Unprotect Password:="********"

Range("A14").Select
Do

If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txttrain.Value
ActiveCell.Offset(0, 1) = txtdate.Value
ActiveCell.Offset(0, 2) = txtresult.Value
ActiveCell.Offset(0, 3) = txtrefresh.Value
ActiveCell.Offset(0, 4) = txttrainer.Value

ActiveSheet.Protect Password:="********"
ActiveWorkbook.Save

Unload Me

frmdataentry.Show
End Sub

--
kiwicolin
------------------------------------------------------------------------
kiwicolin's Profile: http://www.excelforum.com/member.php...o&userid=19861
View this thread: http://www.excelforum.com/showthread...hreadid=473116

--

Dave Peterson

Thanks for the suggestion Dave.

As I am using 2003 I guess I could use monthview instead. It's
certainly worth a go.


Cheers for that.


Colin


--
kiwicolin
------------------------------------------------------------------------
kiwicolin's Profile: http://www.excelforum.com/member.php...o&userid=19861
View this thread: http://www.excelforum.com/showthread...hreadid=473116