Userform help

[K1KKKA]

Hi,

Hope someone can help with this,

i have a userform that the user can input 2 dates and they in turn are
placed into a sheet called "Data", when the Date format is entered
i.e. 11/02/07 meaning 11 Feb 2007, it is shown on the page as being 02
Nov 2007. i have attached the code i am using for the userform and
have tried to use the Format command in various places but to no
avail. any help please ??


Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
iRow = ws.Cells(Rows.Count, 18) _
.End(xlUp).Offset(0, 0).Row
ws.Cells(iRow, 12).Value = Me.date1.Value
ws.Cells(iRow, 13).Value = Me.date2.Value
Me.date1.Value = ""
Me.date2.Value = ""
Me.date1.SetFocus
End Sub


Steve

 

[Mark Ivey]

You need to format the cell before applying the date in the fashion you are
looking to use...

ActiveCell.NumberFormat = "dd/mm/yyyy"

Then apply your data...

 

[Mark Ivey]

Maybe a bit more explanation is needed (please see below). I cannot fully
test it because of all the conditions you have in place, but this should get
you closer...

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
iRow = ws.Cells(Rows.Count, 18) _
.End(xlUp).Offset(0, 0).Row
ws.Cells(iRow, 12).NumberFormat = "dd/mm/yyyy"
ws.Cells(iRow, 12).Value = Me.date1.Value
ws.Cells(iRow, 13).NumberFormat = "dd/mm/yyyy"
ws.Cells(iRow, 13).Value = Me.date2.Value
Me.date1.Value = ""
Me.date2.Value = ""
Me.date1.SetFocus
End Sub

 

[Mark Ivey]

You can play with the formatting to get the desired results you want...


Example: "dd mmm yyyy" ' will return 01 Jan 2007

 

[Mark Ivey]

Let me have just one more go at this one...

I think I see your delimma now...


Try this:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim mydate1, mydate2 As Date
Set ws = Worksheets("Data")
iRow = ws.Cells(Rows.Count, 18) _
.End(xlUp).Offset(0, 0).Row

mydate1 = Format(Me.date1.Value, "dd/mm/yy")
ws.Cells(iRow, 12).Value = mydate1
ws.Cells(iRow, 12).NumberFormat = "dd mmm yyyy"
mydate2 = Format(Me.date2.Value, "dd/mm/yy")
ws.Cells(iRow, 13).Value = mydate2
ws.Cells(iRow, 13).NumberFormat = "dd mmm yyyy"

'ws.Cells(iRow, 12).Value = Me.date1.Value
'ws.Cells(iRow, 13).Value = Me.date2.Value
Me.date1.Value = ""
Me.date2.Value = ""
Me.date1.SetFocus
End Sub

 

[K1KKKA]

Let me have just one more go at this one...

I think I see your delimma now...

Try this:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim mydate1, mydate2 As Date
Set ws = Worksheets("Data")
iRow = ws.Cells(Rows.Count, 18) _
.End(xlUp).Offset(0, 0).Row

mydate1 = Format(Me.date1.Value, "dd/mm/yy")
ws.Cells(iRow, 12).Value = mydate1
ws.Cells(iRow, 12).NumberFormat = "dd mmm yyyy"
mydate2 = Format(Me.date2.Value, "dd/mm/yy")
ws.Cells(iRow, 13).Value = mydate2
ws.Cells(iRow, 13).NumberFormat = "dd mmm yyyy"

'ws.Cells(iRow, 12).Value = Me.date1.Value
'ws.Cells(iRow, 13).Value = Me.date2.Value
Me.date1.Value = ""
Me.date2.Value = ""
Me.date1.SetFocus
End Sub










- Show quoted text -

Thanks worked fine



Steve