+ Reply to Thread
Results 1 to 4 of 4

Dynamic interface

  1. #1
    Registered User
    Join Date
    04-29-2011
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    22

    Dynamic interface

    Hi,

    I'm working on a plugin for excel with a "dynamic" interface. In other words, the contents and layout of the main userform will change during runtime based on user input.

    Overview:

    Basically, the main userform (fMain) consists of a large, empty frame (frmItems) and a commandbutton (cmdAddItem). Clicking this button opens a new userform (fInput), which has a number of check-, text- and comboboxes for the user to fill in, and a commandbutton (cmdOk). When this button is clicked, the following happens:
    • a new instance of the class cItem is created
    • the properties of this new instance are set based on what the user has entered in the various controls on fInput
    • fInput is closed
    • on the main userform a new (smaller) frame is created inside the existing large frame
    • inside this new frame, a number of new controls are added:
    1. two image controls, one depicting a pencil, the other a garbage bin. (These icons are copied from invisible "template" controls.)
    2. a number of labels. The amount and contents will vary based on the properties of the instance.
    When the user clicks the pencil image in any frame, the input form (fInput) is loaded once more, with all controls set to match the properties of the corresponding instance of cItem. In this case, clicking the cmdOk button will of course not create a new instance, a new frame and new controls, but will instead update the properties of the existing instance and will change the number and contents of the existing labels in the corresponding frame on the main form to match these new properties.

    Finally, clicking the garbage bin in any frame will remove that frame and its contents, as well as the matching instance of cItem. If necessary, the other frames will then be repositioned to fill the gap in the large frame.

    Remarks:

    I know that it is generally not advisable to add and remove controls at runtime. It's usually much easier to create any and all potential controls during design, and then simply toggle their visibility as needed during runtime. In this case, however, that's not a feasable solution.

    I also know that controls generated during runtime don't have any code associated with them. It is possible to add the needed code at runtime, but that's a road I'd rather not take. Instead, I use a WithEvents collection to run the same procedure whenever any "pencil"-control is clicked. This procedure first determines with which instance of cItem the clicked control is associated, and then intializes the controls on the input form accordingly. Likewise, when the user is done toggling these controls and closes the input form, the code will identify in which frame on the main form these changes must be implemented (by changing, removing and/or adding labels).

    Question:

    And that's where my question comes in. I'm looking for an efficient and elegant way to connect these dynamically created frames, image controls and labels with the instance of cItem to which they refer (and vice versa).

    Any advice will be greatly appreciated!
    Last edited by nymm; 07-08-2011 at 05:38 PM.

  2. #2
    Registered User
    Join Date
    04-29-2011
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Dynamic interface

    In order to clarify things, I've uploaded a simplified version of the interface.

    As you can see in this example, I currently use the "tag" property of the dynamically created controls to (a) link each frame control to the corresponding instance of cItem and (b) to identify the function of each label within these frames.

    This solution is neither elegant nor efficient, as it requires me to constantly loop through these frames, labels and instances to find a match.

    I've been working on a way to use the "Key" property of collections to create a more direct link between the frames and the instances, by using matching keys in both collections, but I'm sure there are still much better ways of achieving this.

    Again, I would really, really appreciate any advice on this matter!
    Attached Files Attached Files

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Dynamic interface

    I think this will fix your current question.

    Add an Item object based on ClassItem to your ClassPencil

    Please Login or Register  to view this content.
    Then in the Add event code, at the bottom.
    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    04-29-2011
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Dynamic interface

    Thank you for the simple and elegant solution!

+ 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