Late Bound Object Click Events

[AWesner]

Hello,

I concider myself to be a self taught intermediate programmer so bear with me:
I am using Excel 2000 VBA to create a custom dialog to run sorting features
in a spreadsheet. I have a configuration menu consisting of a multipage
object that I have left blank (without any tabs) in design mode. After the
macro is initialized it looks to a sheet in the top row for the names it
needs to create the multipage tabs, below each name is a list of codes that
the type will look for. Each tab denotes a filter name type. On those new
tabs I place listboxes to insert filter codes for those types and I set the
listbox names to "lbxType" & X. where X is the number of the tab it is
currently setting up and I'm hoping to use it as an enumeration tool later.
This is the only way I know of to set this up to be dynamic at runtime as
filter types and codes can come and go and even change order. As far as the
setup process is concerned it runs perfectly. It creates all the tabs and
listboxes and within each listbox all the types are correctly listed. The
idea is to create a nice interface where a user can configure which filters
to use and to add new filter codes when they come along without messing with
the spreadsheet which I'm trying to keep protected from human input errors.

Now, here is the problem I am running into:
I CAN raise a click event for the tabs since the multipage object existed
before runtime and heres a kicker: In the click event for the multipage
object I can have it tell me the index of my selection in the listbox but I
CAN'T seem to raise a click event on any of the listboxes created on the new
tabs. I can pause the macro while it is running and watch all the variables
of the form and see that the names on the listboxes were labeled correctly
but I cannot get it to respond to a Private Sub lbxType1_click() event. I've
also tried an array naming convention for the listboxes and entered the code:
Private Sub lbxType_Click(ByVal Index as Integer) to no avail.
How do I raise a click event for an object created at runtime?

Here is a bit of the code used to load the multipage object:
For reference, arrType is a two dimentional array preloaded with the
category names and associated codes.

ReDim ctrTDesc(UBound(arrType, 1))
ReDim ctrTList(UBound(arrType, 1))
For X = 1 To UBound(arrType, 1)
'Adding a new tab and setting it's name!
frmConfig.mpgType.Pages.Add (arrType(X, 1))
'Creates a label object and sets it to display the filter category name
Set ctrTDesc(X) = frmConfig.mpgType.Pages(X -
1).Controls.Add("Forms.Label.1")
ctrTDesc(X).Left = 5
ctrTDesc(X).Top = 5
ctrTDesc(X).Width = 200
ctrTDesc(X).Height = 25
ctrTDesc(X).Caption = arrType(X, 1)
ctrTDesc(X).FontSize = 10
ctrTDesc(X).Font.Bold = True

'Creates a listbox to input filter codes into
Set ctrTList(X) = frmConfig.mpgType.Pages(X -
1).Controls.Add("Forms.ListBox.1")
ctrTList(X).Name = "lbxType" & X ' my problem seems to revolve
around this but not sure.
ctrTList(X).Left = 5
ctrTList(X).Top = 20
ctrTList(X).Width = 75
ctrTList(X).Height = 150

'Loads the filter codes into the listbox
For Y = 3 To UBound(arrType, 2)
If arrType(X, Y) <> "" Then ctrTList(X).AddItem (arrType(X, Y))
Else Exit For
Next Y
Next X

 

[Bob Phillips]

you have 2 choices IMO

either
create the tabs, listboxes an d the code at design time and hide them

or
add the code dynamically when you add the listboxes

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[AWesner]

I have no experience writing code in runtime but this seems like it would be
the better of the two options.

Thanks

 

[Bob Phillips]

I would do it the other way personally, but I'll play and try and knock you
up some code (if someone else doesn't beat me to it)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[AWesner]

Much thanks. I always enjoy learning something new. It's like an ace up my
sleeve for the next project.

 

[AWesner]

Thanks to your pointer I was able to locate some runetime generated code and
I'll use it as a template for what I need.

Thanks again for your help.

 

[Stephen Bullen]

Hi AWesner,

How do I raise a click event for an object created at runtime?

I would do option #3 - use a class module to handle the events:

Class CListEvents

Public WithEvents mlbList As MSForms.ListBox

Private Sub mlbList_Click()

' your code
End Sub


Then when creating your form, create a new instance of this class for
each list box:

'At the top of the module
Dim mEventClasses As Collection


'At the top of your 'create the tabs' routine
Dim clsListEvents As CListEvents

Set mEventClasses = New Collection

'When creating each list, hook its events

'Creates a listbox to input filter codes into
Set ctrTList(X) = frmConfig.mpgType.Pages(X -
1).Controls.Add("Forms.ListBox.1")

Set clsListEvents = New CListEvents
Set clsListEvents.mlbList = ctrTList(X)
mEventClasses.Add clsListEvents


There are lots of reasons why this way is better than trying to add
code programmatically.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[AWesner]

Thank you,

This is what I needed. I really appreciate this clear answer.