Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Late Binding issue with Excel.Application object | Excel Discussion (Misc queries) | |||
Late binding to Excel from Access causing Object error | Excel Discussion (Misc queries) | |||
Click events on Ecel Cells | Excel Discussion (Misc queries) | |||
Click events on charts | Excel Programming | |||
VBA References - when is Office Object Library Reference set? Best practice re. Early/Late binding ... | Excel Programming |