I am trying to take the date from value of a DTpicker and put it into a cell
on a worksheet. When I do this I get the wrong date in the cell. For
example I take the date from DTPicker by using the following code:

dim subven(5,2) as variant
subven(1, 2) = DTPicker1.Value

and I put it into a cell by using

dim fcell as range
fcell=subven(1,2)

I get the wrong date in fcell. The date in fcell is always 1/0/1900. I
tried doing different number date formats in fcell and I get the same
results. I would like fcell to be in mm/dd/yy format if possible.

How do I get this to work?
Thanks

Can it be as simple as you've forgotten to say which cell fcell is ? This
works fine he

Sub test()

Dim subven(5, 2) As Variant
subven(1, 2) = DateSerial(2005, 12, 25) ' DTPicker1.Value

Dim fcell As Range
Set fcell = Sheets(1).Range("B2")
fcell.Value = subven(1, 2)
End Sub

If you put
Option Explicit
on top of your modules, then then VB editor will provide helpful error
messages in cases like this.

HTH. Best wishes Harald



"ranswrt" skrev i melding
...
I am trying to take the date from value of a DTpicker and put it into a
cell
on a worksheet. When I do this I get the wrong date in the cell. For
example I take the date from DTPicker by using the following code:

dim subven(5,2) as variant
subven(1, 2) = DTPicker1.Value

and I put it into a cell by using

dim fcell as range
fcell=subven(1,2)

I get the wrong date in fcell. The date in fcell is always 1/0/1900. I
tried doing different number date formats in fcell and I get the same
results. I would like fcell to be in mm/dd/yy format if possible.

How do I get this to work?
Thanks

I do say which is fcell. Here is the actual code:

Private Sub UserForm_Terminate()
Dim subven(5, 2) As Variant
Dim db As String
Dim nme As String
Dim cntr As Integer
Dim i As Integer
Dim rng As Range
Dim fcell As Range


db = Range("currentdb")
nme = Replace(db, " ", "")
nme = LCase(nme)
Call stopautocalc

cntr = 1
subven(1, 1) = Label2.caption
subven(1, 2) = DTPicker1.Value

If Label5.Visible = True Then
cntr = cntr + 1
subven(2, 1) = Label5.caption
subven(2, 2) = DTPicker2.Value
End If

If Label7.Visible = True Then
cntr = cntr + 1
subven(3, 1) = Label7.caption
subven(3, 2) = DTPicker3.Value
End If

If Label9.Visible = True Then
cntr = cntr + 1
subven(4, 1) = Label9.caption
subven(4, 2) = DTPicker4.Value
End If

If Label11.Visible = True Then
cntr = cntr + 1
subven(5, 1) = Label11.caption
subven(5, 2) = DTPicker5.Value
End If

Sheets(db & " db").Unprotect Password:="6573"
Set rng = Range(nme & "subvenrng")
For i = 1 To cntr
With rng
Set fcell = .Find(what:=subven(i, 1), LookIn:=xlValues,
LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
fcell.Offset(0, 1) = subven(i, 2)
End With
Next

Sheets(db & " db").Protect Password:="6573"
Call startautocalc

End Sub


Everything works fine except the it always puts the same date in fcell which
is 1/0/1900.

Any ideas what I am doing wrong here?

"Harald Staff" wrote:

Can it be as simple as you've forgotten to say which cell fcell is ? This
works fine he

Sub test()

Dim subven(5, 2) As Variant
subven(1, 2) = DateSerial(2005, 12, 25) ' DTPicker1.Value

Dim fcell As Range
Set fcell = Sheets(1).Range("B2")
fcell.Value = subven(1, 2)
End Sub

If you put
Option Explicit
on top of your modules, then then VB editor will provide helpful error
messages in cases like this.

HTH. Best wishes Harald



"ranswrt" skrev i melding
...
I am trying to take the date from value of a DTpicker and put it into a
cell
on a worksheet. When I do this I get the wrong date in the cell. For
example I take the date from DTPicker by using the following code:

dim subven(5,2) as variant
subven(1, 2) = DTPicker1.Value

and I put it into a cell by using

dim fcell as range
fcell=subven(1,2)

I get the wrong date in fcell. The date in fcell is always 1/0/1900. I
tried doing different number date formats in fcell and I get the same
results. I would like fcell to be in mm/dd/yy format if possible.

How do I get this to work?
Thanks

No, but this code should be pretty easy to debug, using debug.print or
msgboxes, confirming all the values and the cell adresses.
Your code is in the userform_terminate event. Sure that the userform and its
values isn't already terminated ? Debug will show. A buttonclick is the
usual time to perform stuff like this, not terminate, among other things to
give users a Cancel choie.

HTH. Best wishes Harad



"ranswrt" wrote in message
...
I do say which is fcell. Here is the actual code:

Private Sub UserForm_Terminate()
Dim subven(5, 2) As Variant
Dim db As String
Dim nme As String
Dim cntr As Integer
Dim i As Integer
Dim rng As Range
Dim fcell As Range


db = Range("currentdb")
nme = Replace(db, " ", "")
nme = LCase(nme)
Call stopautocalc

cntr = 1
subven(1, 1) = Label2.caption
subven(1, 2) = DTPicker1.Value

If Label5.Visible = True Then
cntr = cntr + 1
subven(2, 1) = Label5.caption
subven(2, 2) = DTPicker2.Value
End If

If Label7.Visible = True Then
cntr = cntr + 1
subven(3, 1) = Label7.caption
subven(3, 2) = DTPicker3.Value
End If

If Label9.Visible = True Then
cntr = cntr + 1
subven(4, 1) = Label9.caption
subven(4, 2) = DTPicker4.Value
End If

If Label11.Visible = True Then
cntr = cntr + 1
subven(5, 1) = Label11.caption
subven(5, 2) = DTPicker5.Value
End If

Sheets(db & " db").Unprotect Password:="6573"
Set rng = Range(nme & "subvenrng")
For i = 1 To cntr
With rng
Set fcell = .Find(what:=subven(i, 1), LookIn:=xlValues,
LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
fcell.Offset(0, 1) = subven(i, 2)
End With
Next

Sheets(db & " db").Protect Password:="6573"
Call startautocalc

End Sub


Everything works fine except the it always puts the same date in fcell
which
is 1/0/1900.

Any ideas what I am doing wrong here?

"Harald Staff" wrote:

Can it be as simple as you've forgotten to say which cell fcell is ? This
works fine he

Sub test()

Dim subven(5, 2) As Variant
subven(1, 2) = DateSerial(2005, 12, 25) ' DTPicker1.Value

Dim fcell As Range
Set fcell = Sheets(1).Range("B2")
fcell.Value = subven(1, 2)
End Sub

If you put
Option Explicit
on top of your modules, then then VB editor will provide helpful error
messages in cases like this.

HTH. Best wishes Harald



"ranswrt" skrev i melding
...
I am trying to take the date from value of a DTpicker and put it into a
cell
on a worksheet. When I do this I get the wrong date in the cell. For
example I take the date from DTPicker by using the following code:

dim subven(5,2) as variant
subven(1, 2) = DTPicker1.Value

and I put it into a cell by using

dim fcell as range
fcell=subven(1,2)

I get the wrong date in fcell. The date in fcell is always 1/0/1900.
I
tried doing different number date formats in fcell and I get the same
results. I would like fcell to be in mm/dd/yy format if possible.

How do I get this to work?
Thanks