Control Array in VBA 1

[SSJpn]

Can I make control array's in VBA? I try copy/pasting textboxes/labels on my form but I can't get that option to make a control array. Is there another way to do it in VBA?

Thanks.

 

[SteveBausch]

Good question, but, unfortunately, no universal answer.


First, I will give my opinions, then give a list of links.

I will guess you know exactly how many, you want to place them all at design-time. You may be satisfied with creating x number of controls, creating "shell" events for each control, that calls the "real deal" events using a parameter to indicate which control.

You really should provide details of your needs, tell us if you need Events, if the controls can be grouped in a frame, if the controls are to be all visible, or if you intend to rotate them through visibility.


Do you need Events with each control? That limits you.
Do you know the exact count at design-time? That could help.


I have successfully worked with issue, but my needs were extremely narrow.

http://www.vbdesign.net/expresso/archive/topic/549.html

http://support.microsoft.com/?kbid=210232

http://www.developerfusion.com/show/116

http://www.developerfusion.com/show/2499

http://www.thescarms.com/vbasic/DynamCtls.asp

http://www.j-walk.com/ss/excel/faqs/xl97faq6.htm

http://archive.devx.com/upload/free/features/vbpj/2000/11nov00/bb0011/bb0011p.asp

http://smsconsulting.spb.ru/shamil_s/topics/ctlarray.htm

http://www.devbuzz.com/content/zinc_evb_control_arrays_pg1.asp

http://www.devbuzz.com/content/zinc_evb_control_arrays_pg2.asp

http://www.msofficemag.net/features/1999/10/vba199910sd_f/vba199910sd_f.asp

http://www.vbcity.com/forums/topic.asp?tid=4612

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20478008.html

 

[SSJpn]

I know the exact number of controls at run-time. I don't need events for each one. I just have 1 command button that will run a loop through each text box and evaluate the text within each text box. (The text boxes are the controls I want as array).

 

[SteveBausch]

Can they all fit in a frame, with nothing else in that frame?

This code will hide the frame, but it's still active

Frame1.SpecialEffect = fmSpecialEffectFlat
Frame1.Caption = vbNullString

You could then do something like:
This code demonstrates the principle

Private Sub Frame1_Click()
Dim OfTheControls As Control
Beep
Debug.Print Frame1.Controls.Count
For Each OfTheControls In Frame1.Controls
Debug.Print OfTheControls.Name
'Debug.Print OfTheControls.Tag 'Optional technique
Next
End Sub

 

[SSJpn]

Yes they can all fit in one frame. But how come I can't just copy/paste the controls like in VB? It doesn't work like that for VBA?

 

[SteveBausch]

And it won't "work like that" in VB.Net, from what I hear.

Take a look at the links I posted, it's a common problem, but not impossible to solve. Just takes some clever programing.

 

[SSJpn]

SteveBausch,

Thanks for all your help so far. I implemented the code as you suggested and it works well. I was wondering, though, how to reference each control specifically?

For example, I want to reference the 'Text' property of the 3rd control in frame frmFrame1. (all the controls are text boxes)

How can I go about doing that?

Thanks,

SSJpn

 

[SteveBausch]

FrmFrame1.Controls(3).Text

 

[foundryqa]

I've have visited most of the links above and have read the code, but can't seem to pull off making an array. I've resigned to having several textboxes sit on a worksheet behind a chart control that I am using. What I would like to do is cycle through a loop that will set each controls position and text. I have a function that is determining the position, the problem that I have is that I don't know how to reference the object. My textbox names are txtRun1, txtRun2 and so on. The first time through the loop I would like to reference txtRunx where x is 1. It seems simple enough, I just can't get the syntax right. Any help would be appreciated.
Thanks in advance,
Fred

 

[Bowers74]

You can't directly create control arrays in VBA like in Visual Basic but there is a workaround:

Dim ctrl As Control
For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "ComboBox" Then
        With ctrl
            .Clear
            .AddItem "Oil"
            .AddItem "Gas"
        End With
    End If
Next ctrl

The above will insert the two items to each ComboBox in the UserForm. You can do the same for the TextBoxes like this:

Dim ctrl As Control
For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "TextBox" Then
        With ctrl
            .Enabled = False
            .Locked = True
        End With
    End If
Next ctrl

The above will Disable and Lock all of the TextBoxes in the UserForm.

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[foundryqa]

Mike,

I modified code similar to your and changed the userform to be my worksheet object. It didn't work. Is there a 'worksheet("me&quot.controls' collection?

 

[Bowers74]

Hey,

I guess I should have read that your controls are located on a sheet. The following code will deliver some information on all of the OLEObjects in your sheet:

Sub WorksheetControls()
Dim oleobj As OLEObject
Dim msg As String
For Each oleobj In ActiveSheet.OLEObjects
    msg = msg & "Object Name: " & oleobj.Name & _
                vbTab & "Type: " & TypeName(oleobj.Object) & _
                vbTab & "Index: " & oleobj.Index & _
                vbTab & "Left: " & oleobj.Left & _
                vbTab & "Top: " & oleobj.Top & vbCrLf
                
Next oleobj
MsgBox msg, vbOKOnly, "Sheet " & ActiveSheet.Name & " Object Information"
End Sub

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[foundryqa]

Thanks Mike.