I am creating a userform by creating listboxes and a textboxes in runtime with in a frame on a form. after submitting the no of required rows by the user, there need to be populate controls in row and column wise as required. The current code generating a single time as I required. All I want to generate it by n number of rows with dynamic linking each other list boxes in row wise after entering a number into the textbox above the frame control.
[number of rows to be entered by user and the column can be constant. eg: user entered 3 in text box, there need to be generate with 2 list boxes in row wise with linking data as shown in 3 rows]
Kindly help me to sort out. Thanks in advance.
The compiled code:
''in userform textbox above the dynamic frame control exit sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
InitializeListBoxes
End Sub
'''General module
Option Explicit
Public LinkedLbxDrives As CLinkedListBox
Public LinkedLbxFolders As CLinkedListBox
Sub InitializeListBoxes()
Dim myFr, myTitle, myBreak As MSForms.Control
Set myFr = UserForm1.Controls.Add("Forms.Frame.1", "Frame", True)
With myFr
.Left = 12
.Top = 90
.Width = 228
.Caption = "Error Details"
End With
Set myTitle = myFr.Controls.Add("Forms.ListBox.1", "Titles", True)
With myTitle
.Left = 12
.Top = 24
.Height = 50
.Width = 75
End With
Set myBreak = myFr.Controls.Add("Forms.ListBox.1", "Breakup", True)
With myBreak
.Left = 108
.Top = 24
.Height = 95
.Width = 100
End With
Set LinkedLbxDrives = New CLinkedListBox
Set LinkedLbxFolders = New CLinkedListBox
Set LinkedLbxDrives.LBX = myTitle
Set LinkedLbxFolders.LBX = myBreak
Set LinkedLbxDrives.NextListBox = LinkedLbxFolders
Set LinkedLbxFolders.NextListBox = Nothing
LinkedLbxDrives.LoadListBox Range("Titles")
End Sub
Sub ClearAllListBoxes()
If LinkedLbxDrives Is Nothing Then
InitializeListBoxes
End If
LinkedLbxDrives.ClearList
End Sub
Sub showFrm()
UserForm1.Show
End Sub
''class module named : CLinkedListBox
-------------------------------------
Option Explicit
Option Compare Text
Option Base 1
Private WithEvents pLBX As MSForms.ListBox
Private pNextListBox As CLinkedListBox
Private pInitialIndex As Long
Public Property Get LBX() As MSForms.ListBox
Set LBX = pLBX
End Property
Public Property Set LBX(C As MSForms.ListBox)
Set pLBX = C
End Property
Public Property Get NextListBox() As CLinkedListBox
On Error Resume Next
Set NextListBox = pNextListBox
End Property
Public Property Set NextListBox(CLbx As CLinkedListBox)
Set pNextListBox = CLbx
End Property
Public Property Get InitialIndex() As Long
InitialIndex = pInitialIndex
End Property
Public Property Let InitialIndex(Value As Long)
If Value < 0 Then
Err.Raise 5, , "InitialIndex must be greater than or equal to 0."
Exit Property
End If
pInitialIndex = Value
End Property
Public Sub LoadListBox(DataRange As Range)
Dim R As Range
Dim Arr() As String
Dim N As Long
N = DataRange.Rows.Count
ReDim Arr(1 To N, 1 To 2)
ClearList
With Me.LBX
N = 0
For Each R In DataRange.Columns(1).Cells
' skip empty elements
If (Len(Trim(R(1, 1).Text)) > 0) Then
N = N + 1
Arr(N, 1) = R.Text
Arr(N, 2) = R(1, 2).Text
End If
Next R
.List = Arr
If .ListCount > 0 Then
If (pInitialIndex <= .ListCount - 1) And _
(pInitialIndex >= 0) Then
.ListIndex = pInitialIndex
Else
.ListIndex = 0
End If
If Not pNextListBox Is Nothing Then
pNextListBox.LoadListBox Range(.List(.ListIndex, 0))
End If
End If
End With
End Sub
Public Sub ClearList()
Me.LBX.Clear
If Not pNextListBox Is Nothing Then
pNextListBox.ClearList
End If
End Sub
Public Sub SetIndex(Value As Long)
Dim S As String
If (Value < 0) Or (Value > Me.LBX.ListCount - 1) Then
Err.Raise 5, , "Invalid Value for ListIndex"
Exit Sub
End If
With Me.LBX
.ListIndex = Value
S = .List(.ListIndex, 0)
pNextListBox.LoadListBox Range(S)
End With
End Sub
Private Sub pLBX_Click()
Dim S As String
With Me.LBX
If .ListIndex < 0 Then
Exit Sub
End If
S = .List(.ListIndex, 0)
If Not pNextListBox Is Nothing Then
pNextListBox.LoadListBox Range(S)
End If
End With
End Sub
Bookmarks