How to dynamically update labels captions in VBA f

问题:

I want to dynamically set the caption for an array of labels (within a VBA form) based on values stored in a worksheet. Thus far I can set them one-by-one like this:

Label1.Caption = MySheet.Range("A1").Value
Label2.Caption = MySheet.Range("B1").Value
Label3.Caption = MySheet.Range("C1").Value ...

Having lots of labels that follow a recurrent pattern, I want to use something smarter, like:

'Method1
For i = 1 To X
    Dim MyLabel as Object: Set MyLabel = "Label" & i
    MyLabel.Caption = MySheet.Cells(i + 1, i).Value
Next i
'Method2
For i = 1 To X
    Label(i).Caption = MySheet.Cells(i + 1, i).Value
Next I
'Both Methods failed. I really appreciate some feedback on this.

回答1:

Use Controls object

For i = 1 To X
    Controls("Label" & i).Caption =  MySheet.Cells(i + 1, i).Value
Next

回答2:

If you want to use this in VBA:

For i = 1 To X
    UserForm1.Controls("Label" & i).Caption =  MySheet.Cells(i + 1, i).Value
Next