Problems with date format in userforms

B

Brian C

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.
 
A

AA2e72E

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.
 
B

Brian C

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
Click to expand...
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.
 
A

AA2e72E

In WORD, verify the language set for Office
Tools | Language | Set Languag

Make English (UK) the default.
 
C

chris

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
 
C

chris: One change

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Dates and Help 0
Userform, Calender, Problems with Todays Date 4
Userform and Text Box Date 2
Date on UserForm? 5
UpperCase changes date format 1
Date Formats 4
Date formatting issue VB 0

Top