Passing Control Names in an Array to another Procedure
How about using a param array and sending the actual controls something like
this...
Sub PassControls()
Call ReceiveControls(True, CheckBox1, CheckBox2, CheckBox3)
End Sub
Sub ReceiveControls(ByVal bln As Boolean, ParamArray ctls() As Variant)
Dim lng
For lng = LBound(ctls) To UBound(ctls)
MsgBox ctls(lng).Name
Next lng
End Sub
Doing this you can send as many or as few controls as you wish...
--
HTH...
Jim Thomlinson
"RyanH" wrote:
I have a UserForm with about 15 Checkboxes, each representing a Department.
By each Department CheckBox I have 4 other controls: DTPicker, Checkbox,
Textbox, and Textbox. I have set all 4 Controls Visible property to False at
Design Time. Here is the code that I have for 1 of the 15 Department
Checkboxes:
Private Sub chkFoamRouter_Click()
' show or hide dept information controls
dtpFoamRouter.Visible = chkFoamRouter
chkFoamRouterDone.Visible = chkFoamRouter
tbxFoamRouterEstHrs.Visible = chkFoamRouter
tbxFoamRouterActHrs.Visible = chkFoamRouter
' enable or disable dept information controls
Call chkFoamRouterDone_Click
Me.Repaint
End Sub
Private Sub chkFoamRouterDone_Click()
' enable or disable dept information controls
dtpFoamRouter.Enabled = Not chkFoamRouterDone
chkFoamRouter.Enabled = Not chkFoamRouterDone
tbxFoamRouterEstHrs.Enabled = Not chkFoamRouterDone
tbxFoamRouterActHrs.Enabled = Not chkFoamRouterDone
End Sub
I have to do this 14 more times! I figured maybe it would be more efficient
and make the workbook file size less if I did it this way:
Private Sub chkFoamRouter_Click()
Call ControlEnabler(Array("dtpFoamRouter", "chkFoamRouterDone", _
"FoamRouterEstHrs", "FoamRouterActHrs"),
chkFoamRouter.Value)
End Sub
Private Sub chkFoamRouter_Click()
Call ControlVisible(Array("dtpFoamRouter", "chkFoamRouterDone", _
"FoamRouterEstHrs", "FoamRouterActHrs"),
chkFoamRouter.Value)
End Sub
Private Sub ControlVisible(ByVal ControlNames As Variant, ByVal vl As Boolean)
Dim i As Long
For i = 0 To 3
ERROR Controls(ControlNames(i)).Visible = vl
Next i
End Sub
Private Sub ControlEnabler(ByVal ControlNames As Variant, ByVal vl As Boolean)
Dim i As Long
For i = 0 To 3
Controls(ControlNames(i)).Enabled = Not vl
Next i
End Sub
This way I could just pass each of my 4 Controls that are next to each
department to a single procedure ControlVisible() and ControlEnabler().
Is this possible? I am getting an error indicated above; "Subscript out of
Range"
--
Cheers,
Ryan
|