ListBox Reference

[glawberc]

Hey guys, all good?

I'm with a kind of a problem by doing a dynamic userform and i'm glad to have the help of you.

I'm working on a excel sheet to do a project management tool, as i dont have any access to MS Project in my company and as a part of this tool i was planning to do a resource management controller for the tasks i have on my project and i thought on do that with an userform that will have a listbox of all resources that will be uploaded by the project management team, but the thing is i just cant add itens to the new listboxs that i've created, could you help me? I'll post the code here and i'd glad if you could help to manage that.

Private Sub UserForm_Activate()

Dim l As Double, r As Double, x As Integer, i As Integer
Dim ctl As Control

'I have utilized this method for adding the pages just one time'

If MultiPage1.Pages.Count < 2 Then
    
    'This count the macro tasks that is listened as integer values on a normal sheet'
    For i = 14 To [Tabela8].Rows.Count
        
        If VarType(Range("A" & i).Value) = vbDouble Then
            ComboBox1.AddItem Range("B" & i).Value
        End If
    
    Next i
        
        'Here i just add the número of pages equivalent to the macro task that i have counted'
        For x = 1 To ComboBox1.ListCount
            MultiPage1.Pages.Add
            Dim MyFrame As Frame
            Set MyFrame = MultiPage1.Pages(x).Add("Forms.Frame.1", "MyFrame", x)
            
            'I also add the frames inside the pages'
            With MyFrame
                .Name = "Frame" & x + 1
                .Top = 6
                .Left = 6
                .Height = 312
                .Width = 678
                
                Dim LTBox
                Set LTBox = .Controls.Add("Forms.ListBox.1")
                
                'and the listbox'
                With LTBox
                    .Name = "ListBox" & x + 1
                    .Top = 40
                End With
            
            End With
    Next
End If

End Sub

I just want to know how i cant reference this new listboxes that i'm creating with the VBA code, because i'll have to add itens to that listbox later. Could you help me?

Thanks a lot,
glawberc

 

[mikerickson]

You are adding lots of list boxes. And not storing them anywhere

Add a module wide collection, before any procedures in the Module (just underneath Option Explicit)

Dim MyListBoxes As Collection

Change this code in the Activate event.

Dim LTBox as MsForms.ListBox
Set MyListBoxes = New Collection

For x = 1 to ComboBox.ListCount
With MyFrame
     .Name = "Frame" & x + 1
     .Top = 6
     .Left = 6
    .Height = 312
     .Width = 678
       
    Set LTBox = .Controls.Add("Forms.ListBox.1")
    With LTBox
         .Name = "ListBox" & x + 1
         .Top = 40
    End With

    MyListBoxes.Add item:=LTBox, key:= LTBox.Name
End With
Next x

Note that the declaration line and the Set myListBoxes = New Collection lines are outside of the loop.

I also note that it appears that all of your created listboxes are put in the same place

Once you have them in the collection, you can refer to a box with sytnax like MyListBoxes("ListBox5").AddItem "first"

If you want code to react to clicking on your created boxes, you'd need to use a Class Module.

 

[glawberc]

Thank you so much mikerickson, this worked very good to my purpose, this method adds the itens perfectly. Thank you!!

 

[glawberc]

Hey mikerickson, i was studying about the class module you've talked about but i didn't figure out how i should put my code in order to add values inside the listboxes i've created. I was thinking in some manners, could you me to set how is the ideal? The image below have te purpose to illustrate what i'll explain next.

View attachment 2291

Basically this MultiPage is inside the userform where that other code above was wrote on, so i'd like to do the AddItem procedure of the ListBox when the user hit that "+" button on upper-right side. My doubt is:

What should i put in my class module in order i could do the same procedure i did with collection?

I have to a personalized "collection" class? Or i must create a procedure inside the class to create the listbox collection and then i do another to add the task inside the listbox?

Could you help me?

Thanks a lot

 

[mikerickson]

The attachment didn't. But you could try something like this.
(Note unless you want events from the added listboxes, I wouldn't use a custom class)

Make a Class module and put this code in it.

' in class module for Class1

Public WithEvents ListBox As MSForms.ListBox

Event Click()

Property Get Name() As String
    Name = ListBox.Name
End Property

Function UFParent() As Object
    Set UFParent = ListBox.Parent
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Function

Private Sub ListBox_Click()
    On Error Resume Next
    Set UFParent.ActiveAddedListBox = Me
    On Error Goto 0
    RaiseEvent Click
End Sub

Note the UFParent function that is needed to pass events back to the userform that holds the list box. This code is for the Click event, but others could be added (except for Enter, Exit, BeforeUpdate and AfterUpdate)

The make a userform with two command buttons and a frame with this code

' in code module for userform

Dim AddedListBoxes As Collection
Public WithEvents ActiveAddedListBox As Class1

Private Sub ActiveAddedListBox_Click()
    With ActiveAddedListBox
        MsgBox .Name & " was clicked." & vbCr & .ListBox.Value & " was selected."
    End With
End Sub

Private Sub CommandButton1_Click()
    Rem make three listboxes in Frame 1
    Dim i As Long
    For i = 1 To 3
        With AddListBox(Frame1).ListBox
            .Width = 80
            .Top = 5
            .Left = (AddedListBoxes.Count - 1) * (.Width + 3)
            .Font.Size = 12
        End With
    Next i
End Sub

Function AddListBox(Optional Container As Object, Optional Name As String) As Class1
    Dim newObj As Class1
    
    If Container Is Nothing Then Set Container = Me
    Set newObj = New Class1
    
    If Name = vbNullString Then
        Set newObj.ListBox = Container.Controls.Add("Forms.ListBox.1", Visible:=True)
    Else
        Set newObj.ListBox = Container.Controls.Add("Forms.ListBox.1", Name:=Name, Visible:=True)
    End If
    
    AddedListBoxes.Add Item:=newObj, Key:=newObj.Name
    Set AddListBox = newObj
    Set newObj = Nothing
End Function

Private Sub CommandButton2_Click()
    Dim i As Long
    If AddedListBoxes.Count = 0 Then
        MsgBox "add some listboxes (press CB 1)"
    Else
        For i = 1 To AddedListBoxes.Count
            With AddedListBoxes(i)
                .ListBox.AddItem "apple " & i
                .ListBox.AddItem "banana " & i
                .ListBox.AddItem "Charlie " & i
            End With
        Next i
    End If
End Sub

Private Sub UserForm_Initialize()
    Set AddedListBoxes = New Collection
End Sub

AddedListBox is a collection of custom objects.
You add items to a ListBox by addressing the ListBox property of one of those custom objects.
Press CommandButton1 to add three ListBoxes to Frame1. Press CommandButton2 to fill them with some items. Click on those items to see event code in action.

Note, the Container argument of the AddListBox function lets you add a listbox to a Page, Frame or (if omitted) to the Userform itself
Note the variable ActiveAddedListBox, it is necessary for the event passing. Another option would be to put your event code inside the custom class, but it would be difficult for that code to "see" the rest of the userform.

 

[glawberc]

Man, this worked very well, i did some adaptations to leave it better for my purposes and all worked very well. Thank you so much!