Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Control Names in an Array to another Procedure
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Control Names in an Array to another Procedure
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. -- Cheers, Ryan "Jim Thomlinson" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Control Names in an Array to another Procedure
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. 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Control Names in an Array to another Procedure
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. -- HTH... Jim Thomlinson "RyanH" wrote: 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. -- Cheers, Ryan "Jim Thomlinson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing sheet names to an array | Excel Programming | |||
Passing an array or recordset to a stored procedure | Excel Programming | |||
Passing an array or recordset to a stored procedure | Excel Programming | |||
Passing an array or recordset to a stored procedure | Excel Programming | |||
Passing properties to a procedure | Excel Programming |