Possible to add a "Private Sub [Listbox]_Click()" event at runtime? 1

[axel67]

I have created an Excel user form (UserForm1) on which I am adding at run-time different listboxes (LB1 to LBn). These listboxes are based on named ranges on the worksheet and can as such vary each time the user form is loaded. I would like to add a click event to a) either all of those listboxes or to each created listbox individually. Both possibilitioes would work, but "a" would be preferred.

I have seen that a click event can be done for command bottons at run-time, but I don't get it for the listbox object....
Anyone knows would be great?!

Cheers, Axel

 

[axel67]

Yeeeeeees it wooooorks (good that there is no sound attached.... )


I did the following change to the code (had to decalare everywhere "MSForms.ListBox" ....):

in the form code:

Private colCB As Collection
Private ctlCB As cCB

Private Sub UserForm_Initialize()
Set colCB = New Collection
Set ctlCB = New cCB
'adding for click testing an item to each box
Me.LB1.AddItem "test1"
Me.LB2.AddItem "test2"
ctlCB.Init Me.LB1, Me
colCB.Add ctlCB
Set ctlCB = New cCB
ctlCB.Init Me.LB2, Me
colCB.Add ctlCB
End Sub

Public Sub Info(ctl As MSForms.ListBox)
MsgBox "click by: " & ctl.ColumnCount 'unfortunately the name property does not work here, but this is not an issue...
End Sub

and in the class module:

Private WithEvents m_CB As MSForms.ListBox
Private m_Form As frmCol

Public Sub Init(ctl As MSForms.ListBox, frm As frmCol)
Set m_CB = ctl
Set m_Form = frm
End Sub

Private Sub m_CB_Click()
m_Form.Info m_CB
End Sub

Private Sub Class_Terminate()
Set m_CB = Nothing
Set m_Form = Nothing
End Sub


Big thanks again to all of you (especially combo!) for the great help!

Cheers,
Axel

 

[combo]

I see I missed quite hot discussion, I'm glad my old post was helpful!

combo