Set vertical scrollbar in dynamic created userform

[YasserKhalil]

Hello everyone
I have the following code that creates a userform dynamicall (labels are from the header row (row 1) & the values of the textboxes are from the active row a way from header row). The columns in the sheet are about 50 columns so there are many labels and textboxes created.
The problem is with the vertical scrollbar (it is created but not active for me), so I can't scroll down all the controls.

Code: Select all

Private Sub UserForm_Initialize()
    Dim headerRange As Range
    Set headerRange = ActiveSheet.Rows(1)
    
    Dim activeRow As Range
    Set activeRow = ActiveCell.EntireRow
    activeRow.Cells(1).Select
    Dim cell As Range
    Dim i As Integer
    
    i = 0
    For Each cell In headerRange.Cells
        If Not IsEmpty(cell.Value) And activeRow.Cells(i + 1).Value <> -2 Then
            i = i + 1
            
            Me.Controls.Add "Forms.TextBox.1", "TextBox" & i, True
            Me.Controls("TextBox" & i).Value = activeRow.Cells(i).Value
            Me.Controls("TextBox" & i).Left = 10
            Me.Controls("TextBox" & i).Top = 10 + (i - 1) * 25
            Me.Controls("TextBox" & i).Width = 200
            Me.Controls("TextBox" & i).TextAlign = fmTextAlignRight
            Me.Controls("TextBox" & i).Font.Bold = True
            
            Me.Controls.Add "Forms.Label.1", "Label" & i, True
            Me.Controls("Label" & i).Caption = cell.Value
            Me.Controls("Label" & i).Left = 220
            Me.Controls("Label" & i).Top = 10 + (i - 1) * 25
            Me.Controls("Label" & i).TextAlign = fmTextAlignRight
            Me.Controls("Label" & i).Font.Bold = True

            Me.Height = 50 + (i - 1) * 25
        End If
    Next cell
    
    Me.ScrollBars = fmScrollBarsVertical
End Sub

Another remark, if the value of the cell was -2 or empty then to skip this field. But I noticed that the code stops at creating labels and textboxes after finding -2 or empty. Is that a bug?

[HansV]

Please attach a sample workbook.

[YasserKhalil]

Here's a sample workbook. Make the active cell in row 4 (as an example)

[HansV]

I'd place the line that sets the Height of the userform below the For ... Next loop - there's no point in changing the height over and over again.

To activate the scrollbar, set the ScrollHeight property of the userform, for example:

Code: Select all

     ...
    Next cell
    
    Me.Height = 50 + (i - 1) * 25
    ' *** New ***
    Me.ScrollHeight = Me.Height
    Me.ScrollBars = fmScrollBarsVertical
End Sub

[YasserKhalil]

Thanks a lot. The vertical scrollbar is solved successfully.
As for the headers, not all labels and textboxes are created. They are created till `Header7` only and then I would like to skip header8 and header9 as they have -2 and empty in the values, but the code should complete the creation of labels and textboxes

Untitled.png

[HansV]

Does this do what you want?

Code: Select all

Private Sub UserForm_Initialize()
    Dim r As Long
    Dim c As Long
    Dim m As Long
    Dim i As Long

    r = ActiveCell.Row
    m = Cells(1, Columns.Count).End(xlToLeft).Column
    For c = 1 To m
        If Cells(1, c).Value <> "" And Cells(r, c).Value <> -2 And Cells(r, c).Value <> "" Then
            i = i + 1
            Me.Controls.Add "Forms.TextBox.1", "TextBox" & i, True
            Me.Controls("TextBox" & i).Value = Cells(r, c).Value
            Me.Controls("TextBox" & i).Left = 10
            Me.Controls("TextBox" & i).Top = 10 + (i - 1) * 25
            Me.Controls("TextBox" & i).Width = 200
            Me.Controls("TextBox" & i).TextAlign = fmTextAlignRight
            Me.Controls("TextBox" & i).Font.Bold = True

            Me.Controls.Add "Forms.Label.1", "Label" & i, True
            Me.Controls("Label" & i).Caption = Cells(1, c).Value
            Me.Controls("Label" & i).Left = 220
            Me.Controls("Label" & i).Top = 10 + (i - 1) * 25
            Me.Controls("Label" & i).TextAlign = fmTextAlignRight
            Me.Controls("Label" & i).Font.Bold = True
        End If
    Next c

    Me.Height = 50 + (i - 1) * 25
    Me.ScrollHeight = Me.Height
    Me.ScrollBars = fmScrollBarsVertical
End Sub

[YasserKhalil]

Amazing. That's exactly what I need.
Thank you very much for great help.

[YasserKhalil]

Can I make the user form height fixed? I tried to use this line `Me.Height = 700` but not all the labels and textboxes are created in that case. And the scrollbar doesn't display all the controls.

[HansV]

Use

Code: Select all

    Me.Height = 700
    Me.ScrollHeight = 50 + (i - 1) * 25

[YasserKhalil]

Thank you very much.
As an idea if possible, is it possible to divide the controls into two parts so as to get a better view of all the controls at the same time?

[HansV]

What do you mean by " divide the controls into two parts"?

[YasserKhalil]

I mean if I have 50 textboxes and labels then to display 25 to the right of the userform and the other 25 to the left.

[HansV]

How about

Code: Select all

Private Sub UserForm_Initialize()
    Dim r As Long
    Dim c As Long
    Dim m As Long
    Dim i As Long
    Dim n As Long
    Dim l As Long
    Dim t As Long

    r = ActiveCell.Row
    m = Cells(1, Columns.Count).End(xlToLeft).Column
    For c = 1 To m
        If Cells(1, c).Value <> "" And Cells(r, c).Value <> -2 And Cells(r, c).Value <> "" Then
            i = i + 1
        End If
    Next c
    n = (i + 1) \ 2

    i = 0
    l = 310
    t = 10
    For c = 1 To m
        If Cells(1, c).Value <> "" And Cells(r, c).Value <> -2 And Cells(r, c).Value <> "" Then
            i = i + 1
            Me.Controls.Add "Forms.TextBox.1", "TextBox" & i, True
            Me.Controls("TextBox" & i).Value = Cells(r, c).Value
            Me.Controls("TextBox" & i).Left = l
            Me.Controls("TextBox" & i).Top = t
            Me.Controls("TextBox" & i).Width = 200
            Me.Controls("TextBox" & i).TextAlign = fmTextAlignRight
            Me.Controls("TextBox" & i).Font.Bold = True

            Me.Controls.Add "Forms.Label.1", "Label" & i, True
            Me.Controls("Label" & i).Caption = Cells(1, c).Value
            Me.Controls("Label" & i).Left = l + 210
            Me.Controls("Label" & i).Top = t
            Me.Controls("Label" & i).TextAlign = fmTextAlignRight
            Me.Controls("Label" & i).Font.Bold = True
            If i = n Then
                l = 10
                t = 10
            Else
                t = t + 25
            End If
        End If
    Next c

    Me.Height = 200
    Me.ScrollHeight = (n + 1) * 25
    Me.Width = 630
    Me.ScrollBars = fmScrollBarsVertical
End Sub

[YasserKhalil]

Really amazing. Wonderful solution. That's exactly what I imagine at first time when I thought of the topic.