VBA Control Arrays HELP

[mrlemmer11]

Hello,

So I have 14 labels on a User Form. When the user form is loaded on stage 1 I want labels 1 thru 8 to be visible with text on them. when a command button is hit, i want labels 2 thru 9 to be visible with the text from the previous label to transfer to the next label, so the original text of label 1 would now be on label two, two would be on three, etc etc.

Sometimes though, the form can be loaded on a different stage... so the user may load it on stage 5 at which point I want labels 5 to 12 be visible and with different captions on them.

I believe I can write the code to have this be a function so I don't need to have 28 statements of .visible and .caption for each command button click, but I run into a situation where I can't seem to put my labels into an array....I need something where this a variation of the following works:

Sub labelSwitch(labelShown as control)
Dim x as integer
x = labelShown.Tag
for n = 1 to UBound(allLabels)
     If allLabels(n).tag = x Then
          allLabels(n).visible = False
          allLabels(n+7).visible = True
          for m = 1 to 7
                  allLabels(m + n).caption = allLabels(m + n -1).caption
          next m
     Else 
     End If
next n

 

[Aaron Blood]

VBA by default, does not support control arrays.

You could write your own Class Module to create a control array... which may be of some interest to you in the future to investigate.

However, for tasks like this I typically take a simpler approach and just use the Tag property of the label as a way to mark controls as belonging to a group. Then I just create little routines to do something to all the controls in the group. So let's simplify a bit and just assume you want to have code to make all label controls visible if their associated tag property is like the format "StageX" where X can be a variable.

My subroutine within the form module might look like this:

Private Sub ShowControlsFor(ByRef KeyWord As String, ByRef n As Integer, Optional Replace As Boolean = True)
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If Replace And ctrl.Tag Like KeyWord & "*" Then
            ctrl.Visible = False
        End If
        If ctrl.Tag Like KeyWord & n Then
            ctrl.Visible = True
        End If
    Next ctrl
End Sub

...then I would add the following reference to the Initialize event of the userform (assuming there is no stage zero) which would set all "Stage" type tags to visible = False

Private Sub UserForm_Initialize()
    ShowControlsFor "Stage", 0
End Sub

The code for a button to display Stage3 tags only would be:

Private Sub CommandButton2_Click()
    ShowControlsFor "Stage", 3
End Sub

The code for a button to display Stage1, 3 and 5 tags only would be:

Private Sub CommandButton3_Click()
    ShowControlsFor "Stage", 1
    ShowControlsFor "Stage", 3, False
    ShowControlsFor "Stage", 5, False
End Sub

Granted, this does require that you actually have label controls on the form with appropriate tag values set at design time. The advantage is that your code doesn't have to change if you just want to add new labels with stages that have already been coded. ...and for my last example there, if there are multiple stages (like 1 to 20) you could increment the n values with a FOR-NEXT loop instead of coding them individually as I did.

If you are still looking for a control array type solution, you may wish to look for "Creating a Control Array in VBA with Class Module"
...or possibly even check out the Collection method as a way to store multiple items for reference.

 

[mrlemmer11]

VBA by default, does not support control arrays.

You could write your own Class Module to create a control array... which may be of some interest to you in the future to investigate.

However, for tasks like this I typically take a simpler approach and just use the Tag property of the label as a way to mark controls as belonging to a group. Then I just create little routines to do something to all the controls in the group. So let's simplify a bit and just assume you want to have code to make all label controls visible if their associated tag property is like the format "StageX" where X can be a variable.

My subroutine within the form module might look like this:

....

Granted, this does require that you actually have label controls on the form with appropriate tag values set at design time. The advantage is that your code doesn't have to change if you just want to add new labels with stages that have already been coded. ...and for my last example there, if there are multiple stages (like 1 to 20) you could increment the n values with a FOR-NEXT loop instead of coding them individually as I did.

If you are still looking for a control array type solution, you may wish to look for "Creating a Control Array in VBA with Class Module"
...or possibly even check out the Collection method as a way to store multiple items for reference.

Aaron Blood, you need to stop being a trooper and become Vader! Upon initial viewing of this I believe it is exactly what I am looking for. I will update as soon as I implement and let you know the results, but I believe it will work beautifully. Thanks so much for the detailed response you put forward!