+ Reply to Thread
Results 1 to 18 of 18

Event Handling for controls created at runtime

  1. #1
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Event Handling for controls created at runtime

    I am having trouble working through the examples given on other posts for this topic. I have a userform that creates a number of controls at runtime. One group of controls is a series of textboxes and a label underneath that sums the values of the textboxes. When a user changes the value in a textbox the label needs to update the sum. I have read about created class modules to define the event but it is all going over my head. A piece of the code that creates the controls is:

    Please Login or Register  to view this content.
    So the controls are stored in an array but how do I handle the change event for each textbox?
    Last edited by jerseyguy1996; 12-16-2009 at 11:10 AM.

  2. #2
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    Does anyone have any insight into this problem?

  3. #3
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: Event Handling for controls created at runtime

    Define a class to handle Textbox WithEvents - specifically the Change event - and create/add each of your textboxes as an instance of this class, rather than an array element as shown in your code. See http://www.ozgrid.com/forum/showthread.php?t=80631 for an example.

  4. #4
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    Quote Originally Posted by Chippy View Post
    Define a class to handle Textbox WithEvents - specifically the Change event - and create/add each of your textboxes as an instance of this class, rather than an array element as shown in your code. See http://www.ozgrid.com/forum/showthread.php?t=80631 for an example.
    I am very confused about a class to handle Textbox WithEvents??? I store each textbox that is created in an array because my continue_click event uses the numbers stored in each textbox to populate a global variable that is used elsewhere in my program. If I add each textbox as an instance of my new class will I still be able to access each one individually to pull out its value? The code example is below to help explain what I am talking about.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Event Handling for controls created at runtime

    Yes you will - you can refer to the textbox as a property of each instance of the class. You can simply loop through the collection as you currently do with your array.
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    Quote Originally Posted by romperstomper View Post
    Yes you will - you can refer to the textbox as a property of each instance of the class. You can simply loop through the collection as you currently do with your array.
    So would each textbox have an index value as in:

    Please Login or Register  to view this content.
    ?

    and my code to loop through could either use the index values or loop through all of them with a:

    Please Login or Register  to view this content.
    ??

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Event Handling for controls created at runtime

    No - you create one instance of the class for each textbox, assign the relevant control to the class's textbox variable and then add the class instance to the collection.
    Then to extract the values, you loop through the collection and refer to each items textbox directly (since each instance of the class only has one textbox variable.)
    Please Login or Register  to view this content.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Event Handling for controls created at runtime

    Here's a demo file that might help clarify the situation. It uses checkboxes, but the principle is the same for any type of control.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    Quote Originally Posted by romperstomper View Post
    No - you create one instance of the class for each textbox, assign the relevant control to the class's textbox variable and then add the class instance to the collection.
    Then to extract the values, you loop through the collection and refer to each items textbox directly (since each instance of the class only has one textbox variable.)
    Please Login or Register  to view this content.
    Ughhh my brain is hurting but I think I am starting to get a handle on this. I am going to try to put this together. Thanks for walking me through this. I am sure I will probably need a bit more hand holding before I get this working 100%.

  10. #10
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: Event Handling for controls created at runtime

    Quote Originally Posted by jerseyguy1996 View Post
    I have a userform that creates a number of controls at runtime. One group of controls is a series of textboxes and a label underneath that sums the values of the textboxes. When a user changes the value in a textbox the label needs to update the sum.
    The code below does exactly that, if I've understood you correctly. Try this in a new workbook.

    Add a Class Module, name it CTextboxEvents and paste this code into it:
    Please Login or Register  to view this content.
    Add a UserForm and put this code in the UserForm1 module:
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    Quote Originally Posted by Chippy View Post
    The code below does exactly that, if I've understood you correctly. Try this in a new workbook.
    So would I want to create a new class for each related group of textboxes? In other words I have a column of textboxes that are created on the fly and at the bottom of those textboxes is my sum label for that column. Next to it I have a second column of textboxes and at the bottom of those is my sum label for that column.

    Or could I theoretically just have one class that holds every control on the form and I would just set an identifying withevent variable for each related group of controls, set my controls equal to those variables and then save the class instance into the collection?

  12. #12
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: Event Handling for controls created at runtime

    Quote Originally Posted by jerseyguy1996 View Post
    So would I want to create a new class for each related group of textboxes? In other words I have a column of textboxes that are created on the fly and at the bottom of those textboxes is my sum label for that column. Next to it I have a second column of textboxes and at the bottom of those is my sum label for that column.

    Or could I theoretically just have one class that holds every control on the form and I would just set an identifying withevent variable for each related group of controls, set my controls equal to those variables and then save the class instance into the collection?
    The latter, though your terminology is wrong. The class doesn't hold anything; it is the instances of the class (i.e. the objects) which hold the custom textboxes, and a collection is used to store the objects - an array could equally be used. The SumLabel property of the class is used to identify which sum label applies to which group of textboxes.

    To demonstrate this, below is another example of UserForm1 code, this time with 2 columns of textboxes and their associated 'sum' labels. Notice that the SumLabel property of each textbox in the first column is set to SumLabel1 (the line Set oTextboxEvts.SumLabel = SumLabel1), and the SumLabel property of each textbox in the second column is set to SumLabel2 (Set oTextboxEvts.SumLabel = SumLabel2). Both SumLabel1 and SumLabel2 are normal label controls added to the userform at run-time. The effect of setting the SumLabel property is that when the oTextbox_Change() event fires, the instance of CTextboxEvents (i.e. an oTextbox object) knows which sum label to update because this label is stored in the pSumLabel member.
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    [QUOTE=Chippy;2216928]The latter, though your terminology is wrong. The class doesn't hold anything; it is the instances of the class (i.e. the objects) which hold the custom textboxes, and a collection is used to store the objects - an array could equally be used. The SumLabel property of the class is used to identify which sum label applies to which group of textboxes.

    To demonstrate this, below is another example of UserForm1 code, this time with 2 columns of textboxes and their associated 'sum' labels. Notice that the SumLabel property of each textbox in the first column is set to SumLabel1 (the line Set oTextboxEvts.SumLabel = SumLabel1), and the SumLabel property of each textbox in the second column is set to SumLabel2 (Set oTextboxEvts.SumLabel = SumLabel2). Both SumLabel1 and SumLabel2 are normal label controls added to the userform at run-time. The effect of setting the SumLabel property is that when the oTextbox_Change() event fires, the instance of CTextboxEvents (i.e. an oTextbox object) knows which sum label to update because this label is stored in the pSumLabel member.
    Please Login or Register  to view this content.
    I have attached a sample of what I am trying to do. Userform3_Initialize is where all of the craziness with the textbox_change events is happening. I have created a commandbutton to launch this part of the program on the sheet.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    Any additional insight into this? Maybe it should go under a new topic since my original problem is solved.

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Event Handling for controls created at runtime

    You just need to add additional variables to your class to hold the additional controls and then assign them to the created objects as you do the original textbox before adding the object to the collection. If you don't need to monitor their events then you don't need to declare the variables WithEvents.

  16. #16
    Forum Contributor
    Join Date
    03-13-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    136

    Re: Event Handling for controls created at runtime

    This is excellent! I appreciate all of the assistance with this.

  17. #17
    Registered User
    Join Date
    03-28-2013
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Event Handling for controls created at runtime

    hi,

    I have created one combo box at design time based on the combobox list index values it will create the controls dynamically(Ex: Listindex o will create 5 comboboxes and 3 text boxes and list index 2 will create 2 comboboxes and one text boxes). Now the challenge is triggering the change event for comboboxes created at run time.

    Any help is greatly appreciated.

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Event Handling for controls created at runtime

    Moorthy,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1