Date from DTPicker

[ranswrt]

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

 

[Harald Staff]

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

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]

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]

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