Shorter way of writing?

[Eric Carolus]

Hi folks.

Is there a shorter way of writing the following?

frmallclasses.lblc1.Caption = Worksheets("Combinations1").Range("C4").Value
frmallclasses.lblc2.Caption = Worksheets("Combinations1").Range("E4").Value
frmallclasses.lblc3.Caption = Worksheets("Combinations1").Range("G4").Value
frmallclasses.lblc4.Caption = Worksheets("Combinations1").Range("I4").Value
frmallclasses.lblc5.Caption = Worksheets("Combinations1").Range("K4").Value
frmallclasses.lblc6.Caption = Worksheets("Combinations1").Range("M4").Value
frmallclasses.lblc7.Caption = Worksheets("Combinations1").Range("O4").Value
frmallclasses.lblc8.Caption = Worksheets("Combinations1").Range("Q4").Value
frmallclasses.lblc9.Caption = Worksheets("Combinations1").Range("S4").Value
frmallclasses.lblc10.Caption = Worksheets("Combinations1").Range("U4").Value
frmallclasses.lblc11.Caption = Worksheets("Combinations1").Range("W4").Value
frmallclasses.lblc12.Caption = Worksheets("Combinations1").Range("U4").Value
frmallclasses.lblc13.Caption = Worksheets("Combinations1").Range("AA4").Value
frmallclasses.lblc14.Caption = Worksheets("Combinations1").Range("AC4").Value
frmallclasses.lblc15.Caption = Worksheets("Combinations1").Range("AE4").Value
frmallclasses.lblc16.Caption = Worksheets("Combinations1").Range("AG4").Value
frmallclasses.lblc17.Caption = Worksheets("Combinations1").Range("AI4").Value
frmallclasses.lblc18.Caption = Worksheets("Combinations1").Range("AK4").Value
frmallclasses.lblc19.Caption = Worksheets("Combinations1").Range("AM4").Value
frmallclasses.lblc20.Caption = Worksheets("Combinations1").Range("AO4").Value
frmallclasses.lblc21.Caption = Worksheets("Combinations1").Range("AQ4").Value
frmallclasses.lblc22.Caption = Worksheets("Combinations1").Range("AS4").Value
frmallclasses.lblc23.Caption = Worksheets("Combinations1").Range("AU4").Value
frmallclasses.lblc24.Caption = Worksheets("Combinations1").Range("AW4").Value
frmallclasses.lblc25.Caption = Worksheets("Combinations1").Range("AY4").Value
frmallclasses.lblc26.Caption = Worksheets("Combinations1").Range("BA4").Value
frmallclasses.lblc27.Caption = Worksheets("Combinations1").Range("BC4").Value
frmallclasses.lblc28.Caption = Worksheets("Combinations1").Range("BE4").Value

It refers to data being written from a sheet called Combinations1 to labels on a form called frmallclasses.
Thank you.

Crow

 

[dmt32]

Hi,
untested & I have only had quick glance at your code but one way maybe

    Dim i       As Long, c As Long
    Dim ws      As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Combinations1")
    
    c = 3
    For i = 1 To 28
        Me.Controls("lblc" & i).Caption = ws.Cells(4, c).Text
        c = c + 2
    Next i

Note: I have replaced the hard coded frmallclasses with the Me keyword as allows you to refer to the object without specifying the name. However, this change does assume that the code is in your Userforms code page.

Dave

 

[Eric Carolus]

Hi Dave

Thanks a million!
It works just fine.

Crow

 

[dmt32]

It works just fine.

Crow

Hi,
glad suggestion helps & appreciate your feedback

Dave