Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime (Latebound) control withevents
My user adds a row of controls (some comboboxes, textboxes, checkboxes) at
runtime. Each row contains a cboIndicator combobox that has the same name except the rownumber is appended to the control name (cboIndicator01, cboIndicator02, ....) When the user changes the cboIndicator value (using the dropdown list), I would like one of the textboxes in the SAME row to receive a value that depends on which cboIndicator value was chosen. I came across Stephen Bullen's code from 12/24/04 for an almost identical problem (see below). However, when I implement this using my generic code to add a row of controls for i = 1 to LastCTLinRow strRowCTLName = arrayWithCTLNames(i,1) strCTLType = arrayWithCTLNames(i,2) Set CTL = frm.Controls.Add(strCTLType, strRowCTLName, Visible) If InStrB(1, LCase$(CTL.Name), "indicator", vbBinaryCompare) Then Set clsListEvents = New CIndListEvents Set clsListEvents.mctCombo = CTL mEventClasses.Add clsListEvents End If it works fine at runtime ONLY for the last row's cboIndicator, which will change that row's correct textbox value. Though this may be a limitation of VBA, I'm sure there is a clever workaround. As I don't know how many rows the user will add, it would be difficult (and inelegant) to code for 30 different rows in advance. I know that lots of people recommend this, but my dynamic code works great as is if someone can help me solve this little problem. As smaller issue (but would help alot, since VBA doesn't have control arrays) is how to tell the class event code (if that is still the best solution) which row's textbox to change. I can do it with a Select Case for every possible number of rows, as I know the textbox name with the rownumber suffix (tboValue01, tboValue02, tboValue03, etc) Stephen Bullen, where art thou? (I own one of your books :) ) Greatly appreciate any help! thanks. 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving MSForm.Control (s) created at Runtime? | Excel Programming | |||
Deleting a textbox control at runtime | Excel Programming | |||
runtime Control and code in VBA | Excel Programming | |||
Changing the height of a commandbar control (edit box) at runtime | Excel Programming |