Change load sequence for dynamic loading userform

[nigelog]

Hi

the attached section of a workbook dynamically loads info to a userform. Now that I have had to add 4 more items of data it has become unwieldy and it would be much better if last 4 headings would load dynamically under one another as there will never be more that 8 of each maximum

the form presently loads 8 columns so it would need to load columns 1 - 3 as per is and then column 4 loading the remaining heading label and data one after another

The code was created by a member here and is above my abilty, I would appreciate if someone could take a look

rgds

[nigelog]

by playing around with settings I have managed to place the first 4 columns in the desired place in userform. Basically the first 3 columns can be handled by the code as is but column 4 needs to first place header label followed by entries from work sheet THEN place label 5 below last entry followed by next etc to last label. Attached workbook might make things clearer. Any help appreciated.

Private Sub CreateLabels()

    Dim iPitch_Column   As Integer
    Dim iEmployeeNo     As Integer
    Dim iColumnNo       As Integer
    Dim iPitch_Row      As Integer
    Dim iRowNo          As Integer
    Dim txt             As MSForms.TextBox
    Dim lbl             As MSForms.Label

    iPitch_Row = miCONTROL_HEIGHT + miSEPARATION
    iPitch_Column = (miWIDTH_LABEL + miSEPARATION + miWIDTH_TEXTBOX) + miSEPARATION_COLUMN

    For iColumnNo = miCOLUMN__SITE_1 To miCOLUMN__SITE_4

        iRowNo = 0

'       Exclude the header row when processing Employee Data
        For iEmployeeNo = LBound(mvaEmployeeData, 1) + 1 To UBound(mvaEmployeeData, 1)

            If Not IsEmpty(mvaEmployeeData(iEmployeeNo, iColumnNo)) Then

                iRowNo = iRowNo + 1

                Set lbl = Me.Controls.Add(bstrProgID:="Forms.Label.1")

                With lbl
'''                    .BorderStyle = fmBorderStyleSingle
                    .Caption = mvaEmployeeData(iEmployeeNo, 1)
                    .Height = miCONTROL_HEIGHT
                    .Width = miWIDTH_LABEL
                    .Left = miLEFT__FIRST_COLUMN + (iPitch_Column * (iColumnNo - 2))
                    .Top = miTOP__FIRST_ROW + (iPitch_Row * (iRowNo - 1))
                End With

                Set txt = Me.Controls.Add(bstrProgID:="Forms.TextBox.1")

                With txt

                    .TextAlign = fmTextAlignCenter
                    .Locked = True
                    .Height = miCONTROL_HEIGHT
                    .Width = miWIDTH_TEXTBOX
                    .Value = Format(mvaEmployeeData(iEmployeeNo, iColumnNo), "dd mmm yy")
                    .Left = lbl.Left + miWIDTH_LABEL + miSEPARATION
                    .Top = miTOP__FIRST_ROW + (iPitch_Row * (iRowNo - 1))

                    If DateValue(.Value) - Now() <= miWarningDays Then

                        txt.BackColor = vbRed
                    End If

                End With

            End If

        Next iEmployeeNo

    Next iColumnNo
'For iColumnNo = miCOLUMN__SITE_5 To miCOLUMN__SITE_8 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End Sub

[nigelog]

By trial and error I can place the header labels in the column with the approximate required spacing. To load the labels and textboxes is there a way I can reference the position of the header label itself to space the data below its own label.

[Greg M]

Hi again,

Thanks for your private message re the above.

See if the attached version of your workbook does what you need.

Regards,

Greg M

[nigelog]

Perfect, you are a gentleman Greg

Pint for you when next in Dublin...

Will mark as solved

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M