Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Controls; setting properties based on cell values
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Controls; setting properties based on cell values
If you could put the control name in an adjacent cell, say E, you could use
code like If Range("T21").Value 0 Then 'Prep OtherEntry form For i = 1 To 4 Me.Controls(Cells(20 + i, "E").Value).Enabled = (Cells(i + 20, "F").Value 0) Next i End If -- HTH RP (remove nothere from the email address if mailing direct) "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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Controls; setting properties based on cell values
Hi
No, Labels are not by default members of a Labels collection, Textboxes are not members of another Textboxes collection. They are all "controls". You can create new collections and put all labels into one custom Labels collection though. That takes about as much coding as you already have, maybe more, so if your goal is less typing then forget it. But if you frequently loop all 160 textboxes on a form for multiple purposes then it's a good idea to do. Post back for code samples if so. HTH. Best wishes Harald "Glenn Ray" skrev i melding ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Controls; setting properties based on cell values
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting cell values based on a 3rd cell | Excel Worksheet Functions | |||
Dynamically setting CELL COLORS based on TWO OTHER cell values | Excel Discussion (Misc queries) | |||
Setting properties of userform controls with VBA | Excel Programming | |||
Setting form controls focus and enter key | Excel Programming | |||
Accessing the values of form controls on worksheets in Excel 2000 | Excel Programming |