Late Bound Object Click Events
 

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

Late Bound Object Click Events
 

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" wrote in message
...
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

Late Bound Object Click Events
 

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

Thanks



"Bob Phillips" wrote:

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" wrote in message
...
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

Late Bound Object Click Events
 

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" wrote in message
...
I have no experience writing code in runtime but this seems like it would
be
the better of the two options.

Thanks



"Bob Phillips" wrote:

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" wrote in message
...
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

Late Bound Object Click Events
 

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

Late Bound Object Click Events
 

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.

Late Bound Object Click Events
 

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

Late Bound Object Click Events
 

Thank you,

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


"Stephen Bullen" wrote:

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