2 ITEMS: Set control/object variable and Get date info

[Mona-ABE]

I have a form with 12 text boxes each named "txtCapacity" & Jan - Dec (in
other words "txtCapacityJan", "txtCapacityFeb", "txtCapacityMar", etc.)

I'm using the code between the dashed lines to try to loop through the
textboxes and set the available work days for each month. I'm getting the
error "object needed" and I know I need to set my object variables (see ???
below), but I'm having no success. I need to include all 12 text boxes
starting with "txtCapacity" in their name.

-------------------
Dim intNumOfWorkDays As Integer

Dim MyObject As Control, MyCollection As Controls

??? set myCollection =
??? set myObject =

For Each MyObject In MyCollection ' Iterate through each element.
If Left(MyObject.Name, 11) = "txtCapacity" Then ' If Name equals
"txtCapacity".

intNumOfWorkDays = dhCountWorkdaysA(Right(MyObject.Name, 3) & " 1,
2006", Right(MyObject.Name, 3) & " 31, 2006", Array(#1/1/2000#, #7/4/2000#))
MyObject.Value = intNumOfWorkDays

Exit For ' Exit loop.
End If
Next
-------------------

Also, I really need help with this part too:

dhCountWorkdaysA([startdate], [enddate], Array([holidays]))

[startdate] should be = Right(MyObject.Name, 3) & [lowestdayinthatmonth] &
[currentyear]

[enddate] should be = Right(MyObject.Name, 3) & [highestdayinthatmonth] &
[currentyear]

How do I calculate the lowest and highest day of the month that is
represented by the last three characters of the textbox's name? And how do I
get the current year?

Please let me know if I should have approached this differently.

 

[Marshall Barton]

I have a form with 12 text boxes each named "txtCapacity" & Jan - Dec (in
other words "txtCapacityJan", "txtCapacityFeb", "txtCapacityMar", etc.)

I'm using the code between the dashed lines to try to loop through the
textboxes and set the available work days for each month. I'm getting the
error "object needed" and I know I need to set my object variables (see ???
below), but I'm having no success. I need to include all 12 text boxes
starting with "txtCapacity" in their name.

-------------------
Dim intNumOfWorkDays As Integer

Dim MyObject As Control, MyCollection As Controls

??? set myCollection =
??? set myObject =

For Each MyObject In MyCollection ' Iterate through each element.
If Left(MyObject.Name, 11) = "txtCapacity" Then ' If Name equals
"txtCapacity".

intNumOfWorkDays = dhCountWorkdaysA(Right(MyObject.Name, 3) & " 1,
2006", Right(MyObject.Name, 3) & " 31, 2006", Array(#1/1/2000#, #7/4/2000#))
MyObject.Value = intNumOfWorkDays

Exit For ' Exit loop.
End If
Next
-------------------

Also, I really need help with this part too:

dhCountWorkdaysA([startdate], [enddate], Array([holidays]))

[startdate] should be = Right(MyObject.Name, 3) & [lowestdayinthatmonth] &
[currentyear]

[enddate] should be = Right(MyObject.Name, 3) & [highestdayinthatmonth] &
[currentyear]

How do I calculate the lowest and highest day of the month that is
represented by the last three characters of the textbox's name? And how do I
get the current year?

It would have been a little easier if you would use the
month number instead of its abbreviation, but with what you
have, the first thing to do is check Help for the DateSerial
function (and any other functions in the below code that you
are not familiar with).

Dim K As Integer
For K = 1 To 12
intNumOfWorkDays = dhCountWorkdaysA( _
DateSerial(Year(Date), K, 1), _
DateSerial(Year(Date), K + 1, 0), _
Array(#1/1/2000#, #7/4/2000#))
Me("txtCapacity" & MonthName(K, True)) = intNumOfWorkDays
Next K

Note that there is no real need for your object variables.
Also note the control reference systax, which is just a
slight variation of formobject.Controls("controlname")

 

[Mona-ABE]

BRILLIANT!!! It works like a charm. Thank you so much for the help!
--
Thanks again!
Mona-ABE

I have a form with 12 text boxes each named "txtCapacity" & Jan - Dec (in
other words "txtCapacityJan", "txtCapacityFeb", "txtCapacityMar", etc.)

I'm using the code between the dashed lines to try to loop through the
textboxes and set the available work days for each month. I'm getting the
error "object needed" and I know I need to set my object variables (see ???
below), but I'm having no success. I need to include all 12 text boxes
starting with "txtCapacity" in their name.

-------------------
Dim intNumOfWorkDays As Integer

Dim MyObject As Control, MyCollection As Controls

??? set myCollection =
??? set myObject =

For Each MyObject In MyCollection ' Iterate through each element.
If Left(MyObject.Name, 11) = "txtCapacity" Then ' If Name equals
"txtCapacity".

intNumOfWorkDays = dhCountWorkdaysA(Right(MyObject.Name, 3) & " 1,
2006", Right(MyObject.Name, 3) & " 31, 2006", Array(#1/1/2000#, #7/4/2000#))
MyObject.Value = intNumOfWorkDays

Exit For ' Exit loop.
End If
Next
-------------------

Also, I really need help with this part too:

dhCountWorkdaysA([startdate], [enddate], Array([holidays]))

[startdate] should be = Right(MyObject.Name, 3) & [lowestdayinthatmonth] &
[currentyear]

[enddate] should be = Right(MyObject.Name, 3) & [highestdayinthatmonth] &
[currentyear]

How do I calculate the lowest and highest day of the month that is
represented by the last three characters of the textbox's name? And how do I
get the current year?

It would have been a little easier if you would use the
month number instead of its abbreviation, but with what you
have, the first thing to do is check Help for the DateSerial
function (and any other functions in the below code that you
are not familiar with).

Dim K As Integer
For K = 1 To 12
intNumOfWorkDays = dhCountWorkdaysA( _
DateSerial(Year(Date), K, 1), _
DateSerial(Year(Date), K + 1, 0), _
Array(#1/1/2000#, #7/4/2000#))
Me("txtCapacity" & MonthName(K, True)) = intNumOfWorkDays
Next K

Note that there is no real need for your object variables.
Also note the control reference systax, which is just a
slight variation of formobject.Controls("controlname")