Is There An Easier Way?

WorkMC

Board Regular
Joined
Jul 21, 2005
Messages
88
I'm using the code below to fill details in on a number of labels within a UserForm. Can the code be made any easier?

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Label1.Caption = Sheets("Data").Range("C1").Value
Label2.Caption = Sheets("Data").Range("D1").Value
Label30.Caption = Sheets("Data").Range("E1").Value
Label3.Caption = Sheets("Data").Range("F1").Value
Label4.Caption = Sheets("Data").Range("G1").Value
Label5.Caption = Sheets("Data").Range("H1").Value
Label6.Caption = Sheets("Data").Range("I1").Value
Label7.Caption = Sheets("Data").Range("K1").Value
Label8.Caption = Sheets("Data").Range("L1").Value
Label9.Caption = Sheets("Data").Range("M1").Value
Label10.Caption = Sheets("Data").Range("N1").Value
Label11.Caption = Sheets("Data").Range("O1").Value
Label12.Caption = Sheets("Data").Range("P1").Value
Label13.Caption = Sheets("Data").Range("Q1").Value
Label14.Caption = Sheets("Data").Range("R1").Value
Label15.Caption = Sheets("Data").Range("S1").Value
Label16.Caption = Sheets("Data").Range("T1").Value
Label17.Caption = Sheets("Data").Range("U1").Value
Label18.Caption = Sheets("Data").Range("V1").Value
Label19.Caption = Sheets("Data").Range("W1").Value
Label20.Caption = Sheets("Data").Range("X1").Value
Label21.Caption = Sheets("Data").Range("Y1").Value
Label22.Caption = Sheets("Data").Range("Z1").Value
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
try:

For i = 1 To 22
Controls("Label" & i).Caption = Sheets("Data").Cells(1, i + 2)
Next i
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
That would work, if you make the columns consistent. There is a discrepancy with Label30 using E1, and nothing using J1. The code above assumes that labels 1 - 22 will always take the text from columns C - Y in that order, Label1 - C, Label2 - D, Label22 - Y etc etc.

Just clean up the label names and the above suggestion should work well !

Bob
 

Watch MrExcel Video

Forum statistics

Threads
1,119,144
Messages
5,576,330
Members
412,716
Latest member
thviid
Top