+ Reply to Thread
Results 1 to 1 of 1

place dynamically created controls in row and column into the userform with in the frame

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    place dynamically created controls in row and column into the userform with in the frame

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Class Module for handling events of dynamically created controls
    By jagman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2018, 01:14 PM
  2. [SOLVED] Loop through userform frame controls
    By c00ly81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2016, 04:38 AM
  3. [SOLVED] Looping through controls by frame on userform (frame within a frame)
    By njs27 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2015, 10:21 AM
  4. [SOLVED] userForm -> Frame 'top' value limited. Dynamically created controls,
    By kropeck in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 08-19-2013, 09:04 AM
  5. Add MouseMove action to dynamically created form controls
    By AdLoki in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-19-2013, 09:09 AM
  6. Dynamically create and Place Controls such as CheckBoxes,Text-Boxes in a UserForm
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 35
    Last Post: 05-11-2011, 11:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1