Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date format in userforms
I'm having trouble with dates entered via a userform. When
I enter a date in the format dd/mm/yyyy everything is ok. I then go and work out the work days between the 2 dates entered. When I next enter the form it shows my date in the format mm/dd/yyyy and if I don't notice and re-enter the date I work out the wrong work days. What do I need to do to get around this problem? Data entered from a userform doesn't seem to take any notice of cell format definitions. The cell is defined as date of type 14/03/2001. The user enters a start and end date then clicks a button which has the associated code: If Not IsDate(Me.TB_A1_SDt.Text) Then MsgBox ("Must be a valid Date!") Me.TB_A1_SDt.Text = "" Me.TB_A1_SDt.SetFocus Else If Not IsDate(Me.TB_A1_ED.Text) Then MsgBox ("Must be a valid Date!") Me.TB_A1_ED.Text = "" Me.TB_A1_ED.SetFocus Else Sheets("AutumnHT1").Cells(2, 1).Formula = CVDate (UserForm1.TB_A1_SDt.Text) Sheets("AutumnHT1").Cells(1, 4).Formula = CVDate (UserForm1.TB_A1_ED.Text) Unload UserForm1 Load UserForm2 UserForm2.Show Exit Sub End If End If End Sub I needed to use the CVDATE function to ensure that it was copied from the userform to the cell as a valid date otherwise it was text. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date format in userforms
What are the 'Long' and 'Short' date formats in regional settings on your PC? Excel uses the regional settings by default and the default for Excel is US i.e. consistent with mm/dd/yyyy. You need to set them to dd/mm/yyyy
Look into: START | SETTINGS | CONTROL PANEL + REGIONAL OPTIONS on the 'Date' tab. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date format in userforms
They're both set to UK settings, i.e. 27/04/2004 for short
and 27 April 2004 for long. Is there somewhere else where dates are set? -----Original Message----- What are the 'Long' and 'Short' date formats in regional settings on your PC? Excel uses the regional settings by default and the default for Excel is US i.e. consistent with mm/dd/yyyy. You need to set them to dd/mm/yyyy. Look into: START | SETTINGS | CONTROL PANEL + REGIONAL OPTIONS on the 'Date' tab. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date format in userforms
In WORD, verify the language set for Office
Tools | Language | Set Languag Make English (UK) the default. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date format in userforms
I am not sure which is the problem: the format when you enter a date, or how a date is Formatted in the textbox, so try this
Convert To your format in the textboxes Afterupdate event Private Sub TB_A1_SDt_AfterUpdate( If Not IsDate(Me.TB_A1_SDt.Text) The MsgBox ("Must be a valid Date!" Me.TB_A1_SDt.Text = "" End i Me.TB_A1_SDt.Text = Format(Me.TB_A1_SDt.Text, "dd/mm/yyyy") End Su ----- Brian C wrote: ---- I'm having trouble with dates entered via a userform. When I enter a date in the format dd/mm/yyyy everything is ok. I then go and work out the work days between the 2 dates entered. When I next enter the form it shows my date in the format mm/dd/yyyy and if I don't notice and re-enter the date I work out the wrong work days What do I need to do to get around this problem Data entered from a userform doesn't seem to take any notice of cell format definitions. The cell is defined as date of type 14/03/2001 The user enters a start and end date then clicks a button which has the associated code If Not IsDate(Me.TB_A1_SDt.Text) The MsgBox ("Must be a valid Date!" Me.TB_A1_SDt.Text = " Me.TB_A1_SDt.SetFocu Els If Not IsDate(Me.TB_A1_ED.Text) The MsgBox ("Must be a valid Date!" Me.TB_A1_ED.Text = " Me.TB_A1_ED.SetFocu Els Sheets("AutumnHT1").Cells(2, 1).Formula = CVDat (UserForm1.TB_A1_SDt.Text Sheets("AutumnHT1").Cells(1, 4).Formula = CVDat (UserForm1.TB_A1_ED.Text Unload UserForm Load UserForm UserForm2.Sho Exit Su End I End I End Su I needed to use the CVDATE function to ensure that it was copied from the userform to the cell as a valid date otherwise it was text |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date format in userforms
Private Sub TB_A1_SDt_AfterUpdate(
If Not IsDate(Me.TB_A1_SDt.Text) Or Me.TB_A1_SDt.Text < "" The MsgBox ("Must be a valid Date!" Me.TB_A1_SDt.Text = "" End i Me.TB_A1_SDt.Text = Format(Me.TB_A1_SDt.Text, "dd/mm/yyyy") End Su ----- chris wrote: ---- I am not sure which is the problem: the format when you enter a date, or how a date is Formatted in the textbox, so try this Convert To your format in the textboxes Afterupdate event Private Sub TB_A1_SDt_AfterUpdate( If Not IsDate(Me.TB_A1_SDt.Text) The MsgBox ("Must be a valid Date!" Me.TB_A1_SDt.Text = "" End i Me.TB_A1_SDt.Text = Format(Me.TB_A1_SDt.Text, "dd/mm/yyyy") End Su ----- Brian C wrote: ---- I'm having trouble with dates entered via a userform. When I enter a date in the format dd/mm/yyyy everything is ok. I then go and work out the work days between the 2 dates entered. When I next enter the form it shows my date in the format mm/dd/yyyy and if I don't notice and re-enter the date I work out the wrong work days What do I need to do to get around this problem Data entered from a userform doesn't seem to take any notice of cell format definitions. The cell is defined as date of type 14/03/2001 The user enters a start and end date then clicks a button which has the associated code If Not IsDate(Me.TB_A1_SDt.Text) The MsgBox ("Must be a valid Date!" Me.TB_A1_SDt.Text = " Me.TB_A1_SDt.SetFocu Els If Not IsDate(Me.TB_A1_ED.Text) The MsgBox ("Must be a valid Date!" Me.TB_A1_ED.Text = " Me.TB_A1_ED.SetFocu Els Sheets("AutumnHT1").Cells(2, 1).Formula = CVDat (UserForm1.TB_A1_SDt.Text Sheets("AutumnHT1").Cells(1, 4).Formula = CVDat (UserForm1.TB_A1_ED.Text Unload UserForm Load UserForm UserForm2.Sho Exit Su End I End I End Su I needed to use the CVDATE function to ensure that it was copied from the userform to the cell as a valid date otherwise it was text |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date format problems | Charts and Charting in Excel | |||
Date format problems ... | Excel Discussion (Misc queries) | |||
Date Format Problems?? | Excel Discussion (Misc queries) | |||
UserForms compatibility problems | Excel Programming | |||
Problems designing userforms | Excel Programming |