Control Arrays in VBA... Anyone?

Tom Schreiner · Mar 23rd, 2002, 05:49 PM

In an earlier response to dantb, Juan posted this message:

Why ? well, in VBA you have Userforms, not forms, they don't have a Load event, they have an Initialize event. You can't (Directly) have array of controls (the optSize looks like OptionButtons).

(Directly) has my interest...

With some of the stuff I do at work, it would be a huge help to me to be able to use control arrays in VBA.

I've heard you can create a form in standalone VB and then call it as a dll.
Do you know of a better alternative?

Thanks

And I appreciate all of the great info I am getting here.

I sure wish I knew about this place a year ago.

[ This Message was edited by: TsTom on 2002-03-23 18:55 ]

[ This Message was edited by: TsTom on 2002-03-24 03:54 ]

[ This Message was edited by: TsTom on 2002-03-24 05:04 ]

Dave Hawley · Mar 23rd, 2002, 08:06 PM

Hi Tom

Quote:
Why ? well, in VBA you have Userforms, not forms, they don't have a Load event, they have an Initialize event.

What's the difference? The "Initialize" Event will only fire when the UserForm is Loaded!

Quote:
You can't (Directly) have array of controls (the optSize looks like OptionButtons).
Don't understand.

Tom, you are making the assumption we are all familiar with your project, which of course we are not.

Tom Schreiner · Mar 24th, 2002, 04:53 AM

Thanks for the reply.
I'll give an example here

On our userform we have 8 text boxes.

In standalone VB you are able to use one name for all 8 by creating a control array.

txtOneName(0).text = "Same Name"
txtOneName(1).text = "Same Name"
txtOneName(2).text = "Same Name"
txtOneName(3).text = "Same Name"
txtOneName(4).text = "Same Name"
txtOneName(5).text = "Same Name"
txtOneName(6).text = "Same Name"
txtOneName(7).text = "Same Name"

This is very convenient because one block of code can take care of all like operations for each text box by simply passing the index number.

As it stands in VBA, I would have to have 8 different names and 8 different blocks of code.

If there is a way to use a control array in VBA, I would like to know how.

Dave Hawley · Mar 24th, 2002, 06:01 AM

Ok, There are a few ways this can be done, but really depends on the reasons. One very easy way is to simply have some code like:

Sheet1.Range("A1")=TextBox1

In the Change Event for any or all Textboxes. Then simply set the ControlSource Property of each to this cell.

I also covered something similar in my latest Newsletter using the Tag Property of TextBoxes. See:
http://www.ozgrid.com/News/Archive.htm
RE: Issue 11

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 23rd, 2002, 05:49 PM	#1
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	In an earlier response to dantb, Juan posted this message: Why ? well, in VBA you have Userforms, not forms, they don't have a Load event, they have an Initialize event. You can't (Directly) have array of controls (the optSize looks like OptionButtons). (Directly) has my interest... With some of the stuff I do at work, it would be a huge help to me to be able to use control arrays in VBA. I've heard you can create a form in standalone VB and then call it as a dll. Do you know of a better alternative? Thanks And I appreciate all of the great info I am getting here. I sure wish I knew about this place a year ago. [ This Message was edited by: TsTom on 2002-03-23 18:55 ] [ This Message was edited by: TsTom on 2002-03-24 03:54 ] [ This Message was edited by: TsTom on 2002-03-24 05:04 ]

Mar 23rd, 2002, 08:06 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Tom Quote: Why ? well, in VBA you have Userforms, not forms, they don't have a Load event, they have an Initialize event. What's the difference? The "Initialize" Event will only fire when the UserForm is Loaded! Quote: You can't (Directly) have array of controls (the optSize looks like OptionButtons). Don't understand. Tom, you are making the assumption we are all familiar with your project, which of course we are not.

Mar 24th, 2002, 04:53 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Thanks for the reply. I'll give an example here On our userform we have 8 text boxes. In standalone VB you are able to use one name for all 8 by creating a control array. txtOneName(0).text = "Same Name" txtOneName(1).text = "Same Name" txtOneName(2).text = "Same Name" txtOneName(3).text = "Same Name" txtOneName(4).text = "Same Name" txtOneName(5).text = "Same Name" txtOneName(6).text = "Same Name" txtOneName(7).text = "Same Name" This is very convenient because one block of code can take care of all like operations for each text box by simply passing the index number. As it stands in VBA, I would have to have 8 different names and 8 different blocks of code. If there is a way to use a control array in VBA, I would like to know how.

Mar 24th, 2002, 06:01 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Ok, There are a few ways this can be done, but really depends on the reasons. One very easy way is to simply have some code like: Sheet1.Range("A1")=TextBox1 In the Change Event for any or all Textboxes. Then simply set the ControlSource Property of each to this cell. I also covered something similar in my latest Newsletter using the Tag Property of TextBoxes. See: http://www.ozgrid.com/News/Archive.htm RE: Issue 11