DTPicker Date format

[ranswrt]

I have the following code that takes the date from 5 DTPickers on a userform
the putts them into cell on a worksheet.

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:="****"
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:="****"
Call startautocalc

End Sub

The problem I am having is that it is putting the wrong date into 'fcell'.
The date it is putting is '1/0/1900' instead of the date that was selected
with the DTPicker control. I have formatted the cell 'date mm/dd/yy'. I
have also tried other number formats. Any suggestions on what to do?

 

[Dave Peterson]

If you put a 0 in A1 and format it as m/d/yyyy, you'll see: 1/0/1900

Maybe you're using the wrong date picker?

 

[Rick Rothstein \(MVP - VB\)]

I'm not so sure I like the logic of your code. Your assignment of DatePicker
dates is contingent on Label controls being visible, so it would seem if the
label is not visible, nothing is assigned to the array for that element and
increment a counter. HOWEVER, when you assign the array elements to the
cells, you just iterate through the counter without worrying about which
DatePicker assignments caused the counter to increase. For example, if the
Label5 and Label7 are not visible and Label9 and Label11 are (it appears
Label2 always is visible), your counter will increment to 3 and the array
assignments will be to subven(1, 2), subven(4, 2) and subven(5, 2). However,
when you assign these to the cells, you will be assigning subven(1, 2),
subven(2, 2), subven(3, 2) because your loop iterates from 1 to cntr (your
counter)... those last two array elements do NOT have dates in them (so I'm
guessing 0 gets assigned to the cells)!

Rick

 

[ranswrt]

I put in a msgbox to see what value I am getting

MsgBox ("DTPicker1.value= " & subven(1, 2))

I am getting a time 12:00:00 instead of a date. I checked the properties
and I have format set to 'dtpShortDate'.

 

[ranswrt]

This is hard to explain why I do it this way but I will try. I have 5 cost
items that get updated occasionly. Not all 5 get updated, but at least one
will when the userform is started. In the userform activate code I set 2
labels and a dtpicker for each cost item that has been changed. The cost
items that have not been changed I set the labels and dtpicker visible to
false. The way the code is written they will always be in order 1,2,3, etc.
so the subven should always been subven(1,2),subven(2,2),etc. It shouldn't
skip the first number in the array. Once the subven array has collected all
the values, I then find the cost item using set fcell = .find******** once it
finds the right cell it then offsets to the correct cell to put the new date
in.

 

[Dave Peterson]

If you format 0 as hh:mm:ss, you'll see 12:00:00.

You're still getting 0 from dtpicker1.

 

[Rick Rothstein \(MVP - VB\)]

If you use your counter to set the array, what you are saying would be true
(I think), but that is not what you are doing. Let's look at the first two
Labels only...

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 Label5 is not visible, NOTHING will be assigned to sebven(2,2), but the
counter will be increased to 2. When you get to this part of your code....

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

when i equals 2, the value in subven(i, 2) will be subven(2,2) and we saw
above the this element of the array does not have a date in it because
Label5 was not visible. I think what you want in each of your
Is-The-Label-Visible tests is this...

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

Notice I am now using your counter to consecutively set the elements of the
array ONLY if the Label is visible. In this example, the ctr is not
incremented and subven(2,1) and subven(2,2) are not assigned anything yet.
Now, lets assume Label7 is visible. When you get to this If-Then block
(modified to use the counter)...

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

the counter is incremented to 2 and subven(2,1) and subven(2,2) will be
assigned values. SO, when you get to the loop, and i=2, there will be values
in it to be assigned.

Rick

 

[ranswrt]

I tried changing to subven(cntr,2) as you suggested and I got the same
results. I think the problem is in the DTPicker value being sent to the
cell. I opened a new sheet and wrote the following code

range("A1")=DTPicker1.value

I didn't format A1 and the value that was put in it was 12:00:00 instead of
a date. I then formatted A1 to date mm/dd/yy and I got 1/0/00. I think the
problem is the value that is being sent to the cell. Any Ideas on how the
get it to sent a date value?
Thanks