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

 

[fumei]

Please clarify. Are you creating the listboxes at run-time, or populating them at run-time?

Listboxes, once created, will have a _Click event.

Gerry
My paintings and sculpture

 

[axel67]

Hi Gerry,
I am creating the boxes at run time (and filling them at run time).

Cheers,
Axel

 

[fumei]

Why are you creating them at run-time? What is the reasoning for creating them at run-time? If they have different items depending on situations, clear them first before populating.

Gerry
My paintings and sculpture

 

[SkipVought]

Hi,

You ought to create the controls in the design mode.

Assign the Visible property FALSE. When you have the list and want the user to see the control, load the list and assign the Visible property TRUE.

Skip,

_[red][/red]

 

[axel67]

I do have a lot of different ranges to load from an initial "excel libary file". I'd rather load them once and have them available afterwards as this performs faster. I do have at the same time treeviews generetaed at run time that display the content of the worksheet ranges. The list boxes hold just the worksheet range names. What I want to achieve is that once an item in the list box is selected (click event); I can trigger to unhide the respective treeview.

If there would be any other generic event on the form (like 'left mose click') that simulates a click on the listbox (or any other control), I could build a workaround with that as well.

Regards,
Axel

 

[fumei]

Huh? I still do not see why you want to create the listboxes at run-time. As Skip says, make them in design mode and use Visible. You can use Visible as part of the Click event of the listbox.

Gerry
My paintings and sculpture

 

[combo]

The standard way to add events to run-time controls is to use WithEvents declaration in a class module. You can create new class module or use existing UserForm module. Next you write event procedures in a standard way and assign your control to WithEvents declared variable at run-time.
This procedure makes sense in case of big or unknown number of controls, otherwise, as it was suggested, you can just hide/unhide existing controls. You can rethink the structure of the userform too, maybe there is more convenient way to present and handle the data. You can modify listbox properties, including linked range.
Some help on WithEvents usage, aside of other stuff, can be found in my vba FAQs.

combo

 

[axel67]

Thanks combo and the others who replied to my question.

Combo, since I am new here, where can I find your VBA FAQ?

Cheers,
Axel

 

[combo]

You can check vba forum FAQ list (button under forum title), or directly faq707-4973, faq707-4974, faq707-4975 and faq707-4976 (there are four parts, covering various ideas).

combo

 

[stevexff]

Huh? I still do not see why you want to create the listboxes at run-time. As Skip says, make them in design mode and use Visible. You can use Visible as part of the Click event of the listbox.

Sorry, Gerry, but I'm with combo on this one. Adding them in design mode is OK up to a point, but you have to decide on an upper limit. What happens when you have n + 1 items and only n prebuilt slots to put them in? Implementing some kind of pagination with <prev> and <next> buttons is awkward.


Also, when you are adding items dynamically, you can do cool¹ things like altering the page size as you go, switching on the scroll bars only when you need them, and other good stuff.

¹ Obviously, this depends on your definition of 'cool'...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[axel67]

Just a little "justification", why I need to generate those listboxes at run time....
I am actally more a business user than a technical user, but I have some "binary" VBA skills. I love Excel for it's great analysis and presentation capabilities, but I am frustrated about the limited possibilities about building hirachies on base data and multi dimensional data turn around. I do work with many different datadumps that have differently organised data. Currently I need to generate "sumif", "{array sum}", "additional help columns" etc, to get information out of those datadumps in order to come to any valuable mgmt decisions.

Since I still need to work for another 20 years (until the home is paid and the kids start to work on their own...), I am going to build something felxible with my BVBA (basic VBA) skills: I am going to build an add-in that let's me manage "hirachy libaries" and within each libary there will be many hirachy dimensions. Once I have set this up it will be a piece of cake (I hope so...) to make a custom formula that adds the numbers of my datadumps according to the libary/hirachies I am feeding in.

Except if any of you know that I can buu something like that, this will be my challenge for my current holidays....

Cheers...Axel

 

[SkipVought]

here's a suggestion.

ONE ComboBox. Yes, ONE.

Lets say a sheet is yout form.

Select A1. The worksheet selection change event positions & sizes the combobox, makes visible and loads with Top Heirarchy list.

User makes selection. The combobox click event stores the selection, makes the combobox invisible & runs a query to return a recordset based on the selected criteria. The combobox is positioned in B1 made visublr & loaded with the appropriate heirarchy list -- etc.

Skip,

_[red][/red]

 

[axel67]

Hi Skip,
I am not sure if I understand your suggestion.
What I would like to do is to generate one Excel file that holds all my different hirachies in named ranges, and I can promise there are many ranges...
The ranges hold the hirachy in two columns: first colum parent, secondcolumn child.

In order to have my data somewhat organised I will name my ranges as follows: [LibaryName].[DimensionName]

A "libary" would represent one or more "datadump file" depending if they use the same dimensions. The dimension name would be for example "base product codes" - that will get a product group hirachy (comes from the named range).

Examples for those could be:
SalesReport.ProductCodes
SalesReport.SalesStaff
SalesReport.Stores
Stockreport.ProductCodes (these codes could be potentially a different syntax than in SalesReport.ProductCodes)
Common.Period (this would be for dimension that are common in all data dumps)

My listboxes on the user form will only hold the libary and dimendsion info (to keep the overview): There will be one listbox hardcoded that shows all the libary names (SalesReport, StockReport, Common,...). Once you click this one (with an easy ..._click procedure ) you get a listbox that shows the dimensions related to that libary. Ie. you click "Salesreport" in the first listbox, it unhides the second list box which shows: ProductCodes, SalesStaff and Stores. Since I never know how many different libaries I will make (in the next 20 years.... ), I am generating this "second visibile" (actually many as it is one listbox per dimension at run time when the form is initalised (loaded). But I am hiding all the listboxes that hold the dimensions of the non-selected libaries from the first listbox.

The trick comes now: If you click (and thats what my problem is...) a dimension in the second listbox, it will show a treeview with the hirachy roll-up of the dimension. These treeviews are also generated at run time, one per "Libary.Dimension".

All of above I have running prefectly except this "bloody" click on the second at runtime generated listbox...
I have tested combos FAQ tip, which works great with the command buttons, but I don't get it running with the Listboxes.

I have modified his code as follows:

On the frmCol userform module I have added two listboxes LB1 & LB2 hardcoded for first testing - but that is where it gets stuck already:

Private colCB As New Collection
Private ctlCB As cCB

Private Sub UserForm_Initialize()
Set ctlCB = New cCB
ctlCB.Init LB1, Me
colCB.Add ctlCB
Set ctlCB = New cCB
ctlCB.Init LB2, Me
colCB.Add ctlCB
End Sub

Public Sub Info(ctl As MSForms.ListBox)
MsgBox "click by: "
End Sub


and on the classmodule cCB:
Private WithEvents m_CB As MSForms.ListBox
Private m_Form As frmCol

Public Sub Init(ctl As 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

Maybe combo knows help????

Thanks,
Axel

 

[SkipVought]

I would advise against having an object (listbox) for each range. You've got one for parent and one for children of the selected parent.

Consider a Sheet as a Table. The Workbook as a database.

You can use MS Query to return a recordset to a sheet or use ADO objects to return a recordset object. I am a big Names Range user, but in this case, I'd go with a database approch.

You would have ONE table (sheet) of ALL your parent/child heirarchal relationships. Then each heirarchal table is a sheet. It's just a matter of substituting one or more criteria values in the appropriate SQL.

So what is not working on the second listbox click? Does the first listbox click not return the subset for the second listbox?

Skip,

_[red][/red]

 

[axel67]

The first listbox click perfectly unhides the "second" listbox. But as per the code above, I do not get the click event for the second list box working.
By the way do you know where to find information about using SQL on "worksheet databases"?

Cheers, Axel

 

[SkipVought]

Using MS Query to get data from Excel faq68-5829

Skip,

_[red][/red]

 

[fumei]

I am confused. I thought this was originally regarding listboxes on a userform, not a sheet.

stevexff - oh I am not saying you should never create userform controls at run-time - if that is indeed what we are talking about.

The OP was about creating a Click event when the listbox is created. This is tricky in a way, or I am way off base here? But does not the creation of a control also create a Click event? The event code is not written, but the event itself now exists. You could, after the creation of the control dynamically write the event code.

What "pagination" are you talking about?

I have made userforms with LOTS of controls, but I use MultiPages and carefully design the work flow through the userform. I have never yet had to add a control at run-time. I do dynamic resizing of the userform depending on current conditions, including controls that sit on top of each other and are replaced to the user's view by judicious .Visible.

Also, when you are adding items dynamically, you can do cool1 things like altering the page size as you go, switching on the scroll bars only when you need them, and other good stuff.

You can alter the page size (by that I assume you mean userform size) if the control item is placed in design mode. It has nothign to do with adding it dynamcially - or am I missing something? Scroll bars can be switched on depending on the current contents of a control - adding it dynamically is not required to do that.

Or, again...am I missing something?



Gerry
My paintings and sculpture

 

[axel67]

Yes Gerry, it is still listboxes on a user form and the code is still hanging....The sample comandbutton code from the FQA page works fine, but when I alter it to listboxes it doesn't do the job anymore. Is this a limit of VBA?

Cheers....Axel

 

[SkipVought]

Gerry,

I probably created the confusion with my suggestion.

Skip,

_[red][/red]