Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format date in excel | Excel Discussion (Misc queries) | |||
Excel keeps converting text to date format | Excel Discussion (Misc queries) | |||
Why Does Date Format Change on Chart | Excel Discussion (Misc queries) | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions | |||
Format Cells - Date options | Excel Discussion (Misc queries) |