Form Controls; setting properties based on cell values
Tom's suggestion was exactly what I was looking for (i.e., "With
frmOtherEntry... .Controls(controlname&number) ).
Bob Phillip's suggestion (i.e. "Me.Controls(...)) The Me keyword is
apparently only available when used with a class module, which my code isn't.
Thanks, Tom.
-Glenn Ray
"Tom Ogilvy" wrote:
With frmOtherEntry
If Range("T21").Value 0 Then
For i = 1 to 10
'Prep OtherEntry form
.Controls("fr" & i).Enabled = _
(Range("F21").Offset(0,i-1).Value 0)
r = -(Range("F21").Offset(0,i-1).Value 0)
.Controls("lbl" & i).ForeColor = _
RGB(128 - (128 * r), 128 - (128 * _
r), 128 + (128 * r))
.Controls("lbl" & i).Caption = _
Format(Range("F11").Offset(0,i-1).Value, _
"mm/dd/yyyy")
Next
End if
End With
--
Regards,
Tom Ogilvy
"Glenn Ray" wrote in message
...
I am currently setting properties for a number of form controls based on
conditions in a worksheet. I've labeled the form controls using numeric
values in the hopes that there may be some way to programmatically
reference
them (as with INDIRECT) and reduce the amount of code.
Here is a sample of the code used so far (I've snipped out middle sections
to reduce size in this example):
If Range("T21").Value 0 Then
'Prep OtherEntry form
frmOtherEntry.fr1.Enabled = (Range("F21").Value 0)
frmOtherEntry.fr2.Enabled = (Range("G21").Value 0)
.
.
.
frmOtherEntry.fr9.Enabled = (Range("Q21").Value 0)
frmOtherEntry.fr10.Enabled = (Range("R21").Value 0)
r = -(Range("F21").Value 0)
frmOtherEntry.lbl1.ForeColor = RGB(128 - (128 * r), 128 - (128
*
r), 128 + (128 * r))
r = -(Range("G21").Value 0)
frmOtherEntry.lbl2.ForeColor = RGB(128 - (128 * r), 128 - (128
*
r), 128 + (128 * r))
.
.
r = -(Range("Q21").Value 0)
frmOtherEntry.lbl9.ForeColor = RGB(128 - (128 * r), 128 - (128
*
r), 128 + (128 * r))
r = -(Range("R21").Value 0)
frmOtherEntry.lbl10.ForeColor = RGB(128 - (128 * r), 128 -
(128
* r), 128 + (128 * r))
frmOtherEntry.lbl1.Caption = Format(Range("F11").Value,
"mm/dd/yyyy")
frmOtherEntry.lbl2.Caption = Format(Range("G11").Value,
"mm/dd/yyyy")
.
.
.
frmOtherEntry.lbl9.Caption = Format(Range("Q11").Value,
"mm/dd/yyyy")
frmOtherEntry.lbl10.Caption = Format(Range("R11").Value,
"mm/dd/yyyy")
fr1 - fr10 are frames in the user form OtherEntry
lbl1-lbl10 are labels with the respective frames
My question is: Is there a way to reference the form controls so that I
could use some form of For-Next loop to set their values relative to those
cell values in the workbook?
|