VBA Excel loop on userform.label

Question

Iam creating my labels and checkbox by code:

i = 1
While Not Sheets("I_M_1_1PW").Cells(9 + i, 43) = "koniec"

    Set theLabel = UserForm1.Controls.Add("Forms.Label.1", labelCounter, True)
    With theLabel
        .Caption = Sheets("I_M_1_1PW").Cells(9 + i, 43)
        .Left = 10
        .Width = 100
        .Top = 13 * labelCounter
        Debug.Print labelCounter & "   " & theLabel.Caption
    End With

Set chkbox = UserForm1.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkbox.Caption = Sheets("I_M_1_1PW").Cells(9 + i, 44)
    chkbox.Left = 100
    chkbox.Width = 75
    chkbox.Top = 13 * labelCounter
i = i + 1
labelCounter = labelCounter + 1

I can search for active check box by code:

For j = 1 To Granica - 1

            If UserForm1.Controls("CheckBox_" & j).Value = True Then

             Wynik1 = UserForm1.Controls("CheckBox_" & j).Caption + Wynik1
    '*         Wzorce = Wzorce + UserForm1.label(j).Caption

            End If
Next

But in '* place i got problem, cant take label.caption when iam using UserForm1.Controls(j).Caption its looping through all parts parts of user.form not only labels.

I think it is not a good idea to give a simple numeric value (ie: labelCounter) as name for your labels. Maybe build a name like you do for checkboxes: UserForm1.Controls.Add("Forms.Label.1", "label_" & labelCounter, True). — Vincent G
– Vincent G, Commented May 4, 2016 at 7:19

user3598756 · Accepted Answer · 2016-05-04 07:27:32Z

you have to use

Wzorce = Wzorce + UserForm1.Controls(CStr(j)).Caption

here follows some other possible enhancements of your code

Dim theLabel As MSForms.Label, chkbox As MSForms.CheckBox
Dim Wynik1 As Variant, Wzorce As Variant

i = 1
While Not Sheets("I_M_1_1PW").Cells(9 + i, 43) = "koniec"

    Set theLabel = UserForm1.Controls.Add("Forms.Label.1", i, True)
    With theLabel
        .Caption = Sheets("I_M_1_1PW").Cells(9 + i, 43)
        .Left = 10
        .Width = 100
        .Top = 13 * i
        Debug.Print i & "   " & theLabel.Caption
    End With

    Set chkbox = UserForm1.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    With chkbox
        .Caption = Sheets("I_M_1_1PW").Cells(9 + i, 44)
        .Left = 100
        .Width = 75
        .Top = 13 * i
    End With

    i = i + 1
Wend

...

For j = 1 To Granica - 1       

  If UserForm1.Controls("CheckBox_" & j).Value = True Then
         Wynik1 = chkboxes(j).Caption + Wynik1
         Wzorce = Wzorce + UserForm1.Controls(j).Caption    
  End If
Next

Finally here follows same code above but with exploitation of control arrays, which can possibly make your code more readable and maintainable:

Dim theLabel As MSForms.Label, chkbox As MSForms.CheckBox
Dim labels() As MSForms.Label, chkboxes() As MSForms.CheckBox

i = 1
While Not Sheets("I_M_1_1PW").Cells(9 + i, 43) = "koniec"

    Set theLabel = UserForm1.Controls.Add("Forms.Label.1", i, True)
    With theLabel
        .Caption = Sheets("I_M_1_1PW").Cells(9 + i, 43)
        .Left = 10
        .Width = 100
        .Top = 13 * i
        Debug.Print i & "   " & theLabel.Caption
    End With
    ReDim Preserve labels(1 To i)
    Set labels(i) = theLabel

    Set chkbox = UserForm1.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    With chkbox
        .Caption = Sheets("I_M_1_1PW").Cells(9 + i, 44)
        .Left = 100
        .Width = 75
        .Top = 13 * i
    End With
    ReDim Preserve chkboxes(1 To i)
    Set chkboxes(i) = chkbox

    i = i + 1
Wend

...

For j = 1 To Granica - 1
    If chkboxes(j).Value = True Then
        Wynik1 = chkboxes(j).Caption + Wynik1
        Wzorce = Wzorce + labels(j).Caption
    End If
Next

End Sub

Rory · Accepted Answer · 2016-05-04 07:20:28Z

0

Assuming your controls are in pairs, it should be:

Wzorce = Wzorce + UserForm1.Controls("" & j).Caption

though I think you should name the labels the same way you did the checkboxes.

answered May 4, 2016 at 7:20

Rory

34.2k5 gold badges40 silver badges42 bronze badges

3 Comments

Vincent G Over a year ago

Is "" & j better than CStr(j) ?

user3598756 Over a year ago

@Rory: sorry, I was editing my answer while you already posted yours. and as long the OP's question is concerned they gave the same solution (though in different "flavours") -> credits to you!

Rory Over a year ago

@VincentG No, not better or worse.

Ghabon · Accepted Answer · 2016-05-04 13:51:54Z

0

Great it works.

Wzorce = Wzorce + UserForm1.Controls("label_" & j).Caption

Ty user3598756.

answered May 4, 2016 at 13:51

Ghabon

112 silver badges6 bronze badges

Collectives™ on Stack Overflow

VBA Excel loop on userform.label

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related