Control Arrays in VBA... Anyone?

L

Legacy 98055

Guest
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top