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