Passing Control Names in an Array to another Procedure

[RyanH]

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"

 

[Jim Thomlinson]

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...

 

[RyanH]

Thanks for the response. I got it to work!

Is using the ParamArray the only way to pass an Array?

In your opinion, is it better to use the ParamArray or do it the old fashion
way like I have earlier?

I am new to VBA and don't really know other peoples styles or making code
work efficiently as possible.

 

[Dave Peterson]

I think your code has a simple mistake (typing!) mistake in it.

But instead of using:
for i = 0 to 3
I'd use:
for i = lbound(controlnames) to ubound(controlnames)

(There's no real good reason to hardcode those numbers and it'll make it a pain
if you decide to enable/disable more than 4 controls.)

I bet you just typed the name of the controls incorrectly.

========
And to make life a bit easier, you could pass it the controls themselves--not
the names.

This worked ok when I had a userform with a handful of checkboxes, textboxes,
listboxes:

Option Explicit
Private Sub CommandButton1_Click()
Call ControlVisible(Array(Me.CheckBox1, _
Me.CheckBox2, Me.TextBox1, Me.ListBox1), False)
End Sub
Private Sub CommandButton2_Click()
Call ControlVisible(Array(Me.CheckBox1, _
Me.CheckBox2, Me.TextBox1, Me.ListBox1), True)
End Sub
Private Sub ControlVisible(ByVal myControls As Variant, ByVal vl As Boolean)

Dim i As Long

For i = LBound(myControls) To UBound(myControls)
myControls(i).Enabled = vl
Next i

End Sub

=====
By using the controls instead of their names, I could let the VBE help me with
the spelling.

 

[Jim Thomlinson]

Since you know that you will be passing 4 objects then you really don't need
the param array. You could hard code your procedures to accept 5 arguments (1
boolean and 4 controls).

You can pass an arry the way you have done it and that will wrok just fine.
I would not hard code upper and lower bounds of the array myself as that is a
recipie for problems when you need to make changes down the road.

The param array is nice because it allow you to pass in as many or as few
arguments as you want to. Think of the sum function. You can pass in as many
or as few ranges as you want to and it will work just fine.