Programmatically add ListBox Event

[Salamander]

Hi all,

I have a small userform which currently programatically adds a multipage page for each open workbook. Onto this new page it adds a new listbox control and populates it with appropriate items.

With Me.MultiPage1
   .Pages.Add
   .Pages(i - 2).Caption = Application.Workbooks(i).Name
   Set lb = Me.MultiPage1.Pages(i - 2).Controls.Add("Forms.Listbox.1")
   For Each ws In Application.Workbooks(i).Worksheets
      lb.AddItem ws.Name
   Next ws
End With

This is all simple enough and works no problem, however:

Is it possible to then create a listbox_DblClick event to this new listbox?

Any suggestions greatly appreciated!


Cheers,

S.

 

[RoryA]

Do all your listboxes do basically the same thing when double-clicked? If so, I would use a class containing a WithEvents Listbox variable, and store each instance of the class in a collection in your userform. For example:
Class module called CListboxHandler:

Option Explicit

Public WithEvents SheetList As MSForms.ListBox

Private Sub SheetList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox SheetList.Value
End Sub

now at the top of your form module add:

Dim colListboxes as Collection

then in the code that creates the listboxes you need something like this:

Dim objCLB as CListboxHandler
Set colListboxes = New Collection
With Me.MultiPage1
   .Pages.Add
   .Pages(i - 2).Caption = Application.Workbooks(i).Name
   Set lb = Me.MultiPage1.Pages(i - 2).Controls.Add("Forms.Listbox.1")
   For Each ws In Application.Workbooks(i).Worksheets
      lb.AddItem ws.Name
   Next ws
   Set objCLB = New CListboxHandler
   Set objCLB.SheetList = lb
   colListboxes.Add objCLB
End With

 

[Salamander]

Thanks Rory - That's absolutely fantastic - works like a charm =]

If so, I would use a class containing a WithEvents Listbox variable, and store each instance of the class in a collection in your userform.

I think I really need to brush up on classes and collections - studied them when I was initially learning to code but have only used them *very* sparingly. This is my homework!

Thanks again =]

S.