Menu
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.
487
Run ShowForm . Click on the form. The caption is changed. Then change the activesheet and click on the form again.
In VBA a "line label" is defined by an identifier followed by a colon, at the beginning of a line of code (and ideally, sitting on its own line): LineLabel: "Label not defined" is the compile error you get when an instruction is referring to a label that doesn't exist in that scope.
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
Use Controls object
For i = 1 To X
Controls("Label" & i).Caption = MySheet.Cells(i + 1, i).Value
Next
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
