[VBA] Handling events on added controls

[mrdominikku]

Hi there,

I am struggling with events in forms. In brief I am passing information between listboxes on each multipage. Number of pages on multipage control depends on number of specific worksheets. So if user has 4 different tabs with different data, form will be initialized with 4 pages. On each page (renamed with worksheet name) 2 listboxes (first listbox list of available headers from tab , second list of selected headers from first list) are copied from first page.
Second list has additionally second column. This column it's very dependent from client specification so need to be changed on spot. I added double click event on second list box which trigger another small form where user can rename or add second column value. Problem is when there is more than 1 tab on multipage. Event works great only on first tab, can't trigger it on each subsequent one.

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    For Each ctrl In Me.MultiPage1.Pages(Me.MultiPage1.Value).Controls
                
        If TypeOf ctrl Is MSForms.ListBox Then
        
            If ctrl.Left = 324 Then
                
                'codehere
                
            End If
            
        End If
        
    Next ctrl
    
End Sub

So when copying page I creat new listboxes (ListBox4,ListBox6...). I have tried to create new event with matched new listboxes names, but it also does not work.

Private Sub ListBox4_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
           Private Sub ListBox6_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Any advise or alternative ?

 

[bobsan42]

Well, from my struggle with similar matters all I can give you is the solution I got to:
I created more pages and more controls. On opening the form the a subroutine was hiding the ones I didn't need.
I used excel to create code for each (more then a 100) control (basically passing the control name to a function) and then pasted it into VBE.

You can change (add/remove) code programatically but sometimes it's just not worth it. Plus it depends a lot on the Trust Center and Security settings.

In summary: Create several tabs/pages, create events for all of them, and then just hide the ones you don't need.

 

[Kyle123]

Adding events to controls added at runtime is pretty easy, you just need to create an object that subscribes to their events - you use a class for this, there are many examples on the web of doing so (for example https://stackoverflow.com/questions...-created-dynamically-in-vba/10596866#10596866)

You can’t just create the handlers in advance, the compiler doesn’t know they’re associated with a control. Also echoing the point above, you don’t want to write code with code. For some reason it’s often suggested as a solution to this pro amen and it’s completly wrong.

 

[mrdominikku]

Well, from my struggle with similar matters all I can give you is the solution I got to:
I created more pages and more controls. On opening the form the a subroutine was hiding the ones I didn't need.
I used excel to create code for each (more then a 100) control (basically passing the control name to a function) and then pasted it into VBE.

You can change (add/remove) code programatically but sometimes it's just not worth it. Plus it depends a lot on the Trust Center and Security settings.

In summary: Create several tabs/pages, create events for all of them, and then just hide the ones you don't need.

@bobsan42 - you rock! the simplest solutions come the hardest!
@Kyle123 - I came across this thread on stackoverlow but it did not meet my expectations, thanks anyway!