Format Several Items in Userform at once

[mattaustin01]

Hi all,

Basically I have a userform that on initialize picks up values from a worksheet and displays them in their appropriate captions. There are often blanks however which makes the form look untidy - I just wondered if there is an easy way for the caption display a hyphen "-" as opposed to just being blank?

I can see a long way round which would be to add an IF statement to each line, or nest several, but is there a quicker way to format any of the captions as "-" if blank?

My Code so far:

Me.Slot1.Caption = Range("A14").Text
Me.Slot2.Caption = Range("A15").Text
Me.Slot3.Caption = Range("A16").Text
Me.Slot4.Caption = Range("A17").Text
Me.Slot5.Caption = Range("A18").Text
Me.Slot6.Caption = Range("A19").Text
Me.Slot7.Caption = Range("A20").Text
Me.Slot8.Caption = Range("A21").Text
Me.Slot9.Caption = Range("A22").Text
Me.Slot10.Caption = Range("A23").Text
Me.Slot11.Caption = Range("A24").Text
Me.Slot12.Caption = Range("A25").Text
Me.Slot13.Caption = Range("A26").Text

Me.Qty1.Caption = Range("D14").Text
Me.Qty2.Caption = Range("D15").Text
Me.Qty3.Caption = Range("D16").Text
Me.Qty4.Caption = Range("D17").Text
Me.Qty5.Caption = Range("D18").Text
Me.Qty6.Caption = Range("D19").Text
Me.Qty7.Caption = Range("D20").Text
Me.Qty8.Caption = Range("D21").Text
Me.Qty9.Caption = Range("D22").Text
Me.Qty10.Caption = Range("D23").Text
Me.Qty11.Caption = Range("D24").Text
Me.Qty12.Caption = Range("D25").Text
Me.Qty13.Caption = Range("D26").Text

Me.Weight1.Caption = Range("E14").Text
Me.Weight2.Caption = Range("E15").Text
Me.Weight3.Caption = Range("E16").Text
Me.Weight4.Caption = Range("E17").Text
Me.Weight5.Caption = Range("E18").Text
Me.Weight6.Caption = Range("E19").Text
Me.Weight7.Caption = Range("E20").Text
Me.Weight8.Caption = Range("E21").Text
Me.Weight9.Caption = Range("E22").Text
Me.Weight10.Caption = Range("E23").Text
Me.Weight11.Caption = Range("E24").Text
Me.Weight12.Caption = Range("E25").Text
Me.Weight13.Caption = Range("E26").Text

Me.Size1.Caption = Range("F14").Text
Me.Size2.Caption = Range("F15").Text
Me.Size3.Caption = Range("F16").Text
Me.Size4.Caption = Range("F17").Text
Me.Size5.Caption = Range("F18").Text
Me.Size6.Caption = Range("F19").Text
Me.Size7.Caption = Range("F20").Text
Me.Size8.Caption = Range("F21").Text
Me.Size9.Caption = Range("F22").Text
Me.Size10.Caption = Range("F23").Text
Me.Size11.Caption = Range("F24").Text
Me.Size12.Caption = Range("F25").Text
Me.Size13.Caption = Range("F26").Text

 

[Andrew Poulsom]

Maybe something like this:

For i = 1 to 13
    With Me.Controls("Slot" & i)
        .Caption = Range("A" & i + 13).Text
        If .Caption = "" Then .Caption = "-"
    End With
Next i

 

[mattaustin01]

That's perfect thanks so much I deleted all the original code I had and now have the following:

For i = 1 To 13
    With Me.Controls("Slot" & i)
        .Caption = Range("A" & i + 13).Text
        If .Caption = "" Then .Caption = "(none)"
    End With
Next i
For i = 1 To 13
    With Me.Controls("Qty" & i)
        .Caption = Range("D" & i + 13).Text
        If .Caption = "" Then .Caption = "-"
    End With
Next i
For i = 1 To 13
    With Me.Controls("Weight" & i)
        .Caption = Range("E" & i + 13).Text
        If .Caption = "" Then .Caption = "-"
    End With
Next i
For i = 1 To 13
    With Me.Controls("Size" & i)
        .Caption = Range("F" & i + 13).Text
        If .Caption = "" Then .Caption = "-"
    End With
Next i

 

[Andrew Poulsom]

Good, but you only need one loop.

 

[mattaustin01]

How will it work with only one loop?

I need to do it with all the Slot, Qty, Weight & Size captions