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?

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?

ranswrt wrote:
>
> 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

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" <(E-Mail Removed)> wrote in message
news:497327A2-2EFB-4A29-AD16-(E-Mail Removed)...
>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?

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

"Dave Peterson" wrote:

> 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?
>
> ranswrt wrote:
> >
> > 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
>

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.

"Rick Rothstein (MVP - VB)" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:497327A2-2EFB-4A29-AD16-(E-Mail Removed)...
> >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?
>
>

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

You're still getting 0 from dtpicker1.



ranswrt wrote:
>
> 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'.
>
> "Dave Peterson" wrote:
>
> > 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?
> >
> > ranswrt wrote:
> > >
> > > 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
> >

--

Dave Peterson

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" <(E-Mail Removed)> wrote in message
news:0949706B-7C8A-41EB-AC59-(E-Mail Removed)...
> 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.
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:497327A2-2EFB-4A29-AD16-(E-Mail Removed)...
>> >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?
>>
>>

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

"Rick Rothstein (MVP - VB)" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0949706B-7C8A-41EB-AC59-(E-Mail Removed)...
> > 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.
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:497327A2-2EFB-4A29-AD16-(E-Mail Removed)...
> >> >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?
> >>
> >>
>
>