Solved: More array questions

[jamescol]

I'm thinking I outsmarted myself. When I began outlining this OL2003 app, I named my control suffixes to match the consts I use in the following array.

[VBA]
Const DAY180 = 180
Const DAY120 = 120
Const DAY90 = 90
Const DAY60 = 60
Const DAY30 = 30
Const ExpireDay = 1

arrayRenewalControlNames = Array(DAY180, DAY120, DAY90, DAY60, DAY30, ExpireDay)
[/VBA]

Thanks to the help in an earlier thread, this array is behaving as I need returning the Const values as integers used in date calculations after I removed the "" marks from the CONST in the array.

What I also intended to do with this array was use it in a loop to construct the TextBox and Checkbox control names I placed on the custom form. The side effect of removing the "" marks from the array elements prevents the following code from working. I get a Type Mismatch error.

[VBA]
Dim i As Integer
For i = LBound(arrayRenewalControlNames) To UBound(arrayRenewalControlNames)

If arrayRenewalDates(i, 1) = vbNull Then
'There was no date set, so disable the controls

With "txt" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Value = arrayRenewalDates(i, 1)
.Enabled = False
.BackColor = vbGrayText
End With

With "cbox" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Enabled = False
.Value = arrayRenewalDates(i, 2)
.BackColor = vbGrayText
End With

Else

'Set the date and enable the controls

With "txt" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Value = arrayRenewalDates(i, 1)
.Enabled = True
.BackColor = vbWindowBackground
End With

With "cbox" & arrayRenewalControlNames(i) 'Build the control name used on the form
.Enabled = True
.Value = arrayRenewalDates(i, 2)
.BackColor = vbWindowBackground
End With

End If
Next i[/VBA]

I tried casting the array element to get around the type mismatch by using:

[VBA]
With "txtDay" & CStr(arrayRenewalControlNames(i))

AND

With "txtDay" & Str(arrayRenewalControlNames(i))
[/VBA]

Both give me an error message that I must declare an object, etc. When I examine the resulting concatenation in the immediate window, the name constructs correctly, so I am not sure why I get the error message.

So I am seeking suggestions for resolving my need to use both the CONST values in one procedure and the CONST names in another - hopefully without creating another array for each set of controls.

Thanks for any advice!

James

[mark007]

The problem is you are referring to a string rather than an object in your with statement. Replace your with lines with:

[vba]
With Me.Controls("txt" & arrayRenewalControlNames(i))
[/vba]

etc.

[jamescol]

Mark - right on the money! I'm cruising now...

Thanks!
James

[Anne Troy]

Can we mark this one solved, then, James?
What about the second one? Is that solved?

[jamescol]

Dreamboat - yes, both are resolved!

[mark007]

Glad I could help.