VBA, Control Array's

Control Array's - Help

Author	Message
Neil JG Partridg #1 / 4	Control Array's - Help I am relatively new to VBA (I tend to use VB). I am trying to develop an application that shows all the weeks in the Year - effectively 52 Label boxes at the top of my form. As this is a little unwieldy, I have inserted a list box that allows you to select a week number - this then hides all the previous weeks by setting the label.visible to false. This has meant a lot of code. I would rather do this by the preffered method and use a control array. VBA (access) will not let me do this - I cannot seem to manipulate multiple controls in one quick swoop. In VB5 I would use the control array method i.e. for n=0 to 5 cmdbutton(n).width = 4000 next n This (I'm sure its fairly apparent) would set the width automatically on 6 command buttons to 4000. I cannot seem to do this in VBA and no one seems to know how to do it. If anyone knows how to get around this, please email me and let me know. It's driving me up the wall....
Tue, 01 May 2001 03:00:00 GMT

Michel Wals #2 / 4	Control Array's - Help Hi, More practical, and if possible in your case, is to go the database way: make a table with two fields, one for the week number, one for the check box. In the first time, the user check/uncheck the desired weeks and, once done, make a query, JOINning that table to the original data, through the week number, and present the resulting recordset in a continuous form view. The "database" way is simply stated as "avoid an arrangement where a modification will imply touching again the code". As example, there are years with 53 weeks, so, what you do with fixed labels? With a table in a database, just add a record to a table, no need to touch the code again. Similar, imagine a form with three radio buttons. What happen if a fourth one is required? And you modify the form, and maybe the code. Replacing those with a combo-box where the list allow a selection with a fourth or a fifth option easily added without changing any piece of the GUI neither a single line of the code. Same thing for the laws of normalization of data, some can be seen as an extension of that principle. Hope it may help, Vanderghast, Access MVP. Quote: >I am relatively new to VBA (I tend to use VB). I am trying to develop an >application that shows all the weeks in the Year - effectively 52 Label >boxes at the top of my form. As this is a little unwieldy, I have inserted a >list box that allows you to select a week number - this then hides all the >previous weeks by setting the label.visible to false. This has meant a lot >of code. I would rather do this by the preffered method and use a control >array. VBA (access) will not let me do this - I cannot seem to manipulate >multiple controls in one quick swoop. >In VB5 I would use the control array method i.e. >for n=0 to 5 >cmdbutton(n).width = 4000 >next n >This (I'm sure its fairly apparent) would set the width automatically on 6 >command buttons to 4000. I cannot seem to do this in VBA and no one seems to >know how to do it. If anyone knows how to get around this, please email me >and let me know. It's driving me up the wall....
Tue, 01 May 2001 03:00:00 GMT

Terry Kref #3 / 4	Control Array's - Help Hi Neil, Ther are no control arrays in Access, you can fudge it though. Name all the labels lblWeekX where X represents a number form 1 to 52 Then Dim intx as integer dim strLabel as string for intx = 1 to 52 strlabel = "lblweek" & cstr(intx) me(strlabel).width = 300 next not as clean as a control array but as close as you'll get in VBA. Quote: >I am relatively new to VBA (I tend to use VB). I am trying to develop an >application that shows all the weeks in the Year - effectively 52 Label >boxes at the top of my form. As this is a little unwieldy, I have inserted a >list box that allows you to select a week number - this then hides all the >previous weeks by setting the label.visible to false. This has meant a lot >of code. I would rather do this by the preffered method and use a control >array. VBA (access) will not let me do this - I cannot seem to manipulate >multiple controls in one quick swoop. >In VB5 I would use the control array method i.e. >for n=0 to 5 >cmdbutton(n).width = 4000 >next n >This (I'm sure its fairly apparent) would set the width automatically on 6 >command buttons to 4000. I cannot seem to do this in VBA and no one seems to >know how to do it. If anyone knows how to get around this, please email me >and let me know. It's driving me up the wall....
Tue, 01 May 2001 03:00:00 GMT

César Moren #4 / 4	Control Array's - Help This question was asked in October during the VBA Conference in Los Angeles to the top guys from MS. Their answer as simple as..."There are no Control Arrays in VBA". So the problem is not from Access. The reason why there are no Control Arrays is that VB uses a different MS Forms than VBA, that same reason explains why you do not have in VBA Shape or Line controls. They also said that they didn't plan to make VBA forms compatible with VB forms because in the future...after Office 200 will be to develop forms using Dynamic HTML. Cesar Moreno University of Advancing Computer Technology Tempe, AZ Quote: >Hi Neil, >Ther are no control arrays in Access, you can fudge it though. >Name all the labels lblWeekX where X represents a number form 1 to 52 >Then > Dim intx as integer > dim strLabel as string > for intx = 1 to 52 > strlabel = "lblweek" & cstr(intx) > me(strlabel).width = 300 > next >not as clean as a control array but as close as you'll get in VBA. >>I am relatively new to VBA (I tend to use VB). I am trying to develop an >>application that shows all the weeks in the Year - effectively 52 Label >>boxes at the top of my form. As this is a little unwieldy, I have inserted >a >>list box that allows you to select a week number - this then hides all the >>previous weeks by setting the label.visible to false. This has meant a lot >>of code. I would rather do this by the preffered method and use a control >>array. VBA (access) will not let me do this - I cannot seem to manipulate >>multiple controls in one quick swoop. >>In VB5 I would use the control array method i.e. >>for n=0 to 5 >>cmdbutton(n).width = 4000 >>next n >>This (I'm sure its fairly apparent) would set the width automatically on 6 >>command buttons to 4000. I cannot seem to do this in VBA and no one seems >to >>know how to do it. If anyone knows how to get around this, please email me >>and let me know. It's driving me up the wall....
Wed, 02 May 2001 03:00:00 GMT

Page 1 of 1

[ 4 post ]