Geek Answers Handbook

How to dynamically update label headers in a VBA form?

I want to dynamically set the title for an array of labels (in VBA form) based on the values ​​stored in a worksheet. So far I can install them one by one as follows:

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

With many shortcuts that follow the repeating pattern, I want to use something smarter, like this:

 '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.
+6
source share
3 answers

Use Controls Object

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

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
+3
source 
 For i = 1 To X UserForm1.Controls("Label" & i).Caption = MySheet.Cells(i + 1, i).Value Next

This code throws an error in the "controls". Does anyone know why?

0
source

More articles:


All Articles