Set a Control Object Via a Variable

[jasonp45]

I have some controls on a VB form, and I want to set properties based upon a variable. More specifically...I have a series of checkboxes in Access VBA, and I want to loop through an array and for each value reset the caption of the checkbox labels. So my labels are named 'lbl1', 'lbl2', etc. (since Access seemingly won't let me create a control array), and as my loop executes I'd like to say:

Dim oCtrl as Control
Set oCtrl = "lbl" & a

This of course doesn't work since the datatypes don't match. The only way I've succeeded in doing something along these lines is by looping through all of the controls on my form and using the Control.Name property to compare against my string variable...but it seems that there must be a more elegant way.

 

[Databaseguy]

No, VBA doesn't allow Control Arrays

How 'bout something like this:

Private Sub NameControl(frm As Form)
    Dim ctl As Control
    For Each ctl In frm.Controls
        With ctl
            Select Case .ControlType
                Case acCheckBox
                    .Name = YourCodeHere
                Case Else
                    'Something else
            End Select
    Next ctl
End Sub

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[JohnYingling]

If you were regular VB I would say use CallByName BUT a Scripting.Dictionary will work in VBA, at least for me.

Private dctd as scripting.Dictionary
....
Set dctD = New Scripting.Dictionary
dctD.CompareMode = vbTextCompare
Dim ctl as object
For Each objctl in Forms.Control
    Set dctD(ctl.Name) = ctl
Next
' Now name of control can fetch the reference to the control
Set ctl = dctD("lbl" & a)

http://www.VBCompare.com

http://www.VBSortGen.com