Dynamic Userform Design

    Hi all,


    I have been playing around with userforms and had a design question. Currently I have a userform which the user populates with ten different items per line of stock. They then click a button to add another line and in doing so adds ten textboxes for the next line of stock. The problem is I have code behind each text box which though automatically gets written (and works) I'm worried that the code will get too big if the user adds in say 100 lines (therefore there would be 1000 text boxes generated, each with "onexit" code written in a loop (basically the entered information gets stored on a sheet and then other fields get triggered to update with formula)


    Am I asking for trouble taking this approach? The user wants to see the updates as they type into the userform so want to avoid a "refresh" button approach. Is there a way of saying on any field exit in the userform, loop through all the controls and write to sheet/default values as per the code? I read about class modules put it seems they only work on change events and I don't want code being triggered on each key stroke, only when they have finished inputting for that field (though Id have to see how slow that would be taking that approach).


    I will hasten to add it is unlikely that there will be more than 20 lines/200 boxes per user form, but the code generated behind that would still be quite large and go over the module size.


    Any suggestions welcome.

    Re: Dynamic Userform Design


    I don't know if it suits you, but did you try to use a listbox instead of textboxes?
    You can have a a simple line of your textboxes and then 2 or 3 buttons to add / edit /delete on your listbox.

    Re: Dynamic Userform Design


    Quote from nikolassor;783955

    I don't know if it suits you, but did you try to use a listbox instead of textboxes?
    You can have a a simple line of your textboxes and then 2 or 3 buttons to add / edit /delete on your listbox.


    Hi,


    Thanks for your thoughts. However, wouldnt the listbox still have to have code written for each one, just like the textbox? Like i say, my approach works intitally, but for each product line added, a 100 lines of code get generated and there is a danger of the userform code being too large. I dont like designing something knowing it could break.


    what i was hoping is there to be a generic way to say on exit of any box on the userform, then check the name of the box and depending on what it was, add in formula or defaults. I think i read about adding a collection, but the limit on using that is only on change rather than on exit of the box (which if we have a 500 boxes generated could be seriously laggy on every button press).


    let me know if im not making sense. Im getting worried that ive gone down a route which wont work.

    Re: Dynamic Userform Design


    Maybe this can help you. If I understand correct, you have to put all your textboxes inside a frame and use a class on frame_ExitEvent.


    (With a listbox you don't have to check the values inside your list. Just the textboxes that you use to enter your data...)

    Re: Dynamic Userform Design


    Possbly you may be my latest best friend. This is my job for next day or so, thank you so much. Will post back after playing.

    Re: Dynamic Userform Design


    As an alternative, you could put your code in a separate place (module) and then invoke it with a single line of code from your textbox - that way you'll only be adding one line of code for each control (OK, 3 including the Sub and End Sub lines)

    Re: Dynamic Userform Design


    Why have text boxes for every line of stock to be entered?


    Just have 10 text boxes that the user can use to enter data for the stock item to be entered, or edited (whichever you are doing) and a command button.


    After entering data in the text boxes the user can check that all the entries are correct, then click the command button which will upload all text boxes to the worksheet database and clear the text boxes ready for the next stock item data entry.


    You could even have 2 command buttons, one to "Submit and do another stock item" and the other to "Submit and quit the user form".

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Dynamic Userform Design


    Fair point, but the person who will be using it is very old school, they are doing it by pen and paper currently! Im actually designing an invoice which includes various calculations for steel beams. There is an element of "fudge" so they like to see all the calcs in real time and overtype if needed at the end or as and when. Hence i have added scroll bars for when lines get added. There are buttons to then finalise, and that converts it into a pdf. It is unlikely that there will be any more than 30 lines, but i dont like building something knowing it can break. The link posted seems very useful, and looks like it will work, though this is abit new for me. Its actually a bit sad that this guy has literally spent years working out the invoices when excel will get it down to a few minutes.


    i havent really had need to play with userforms previously so this was also a good excuse to play around with them. Hope that makes sense.

    Re: Dynamic Userform Design


    Can you attach your workbook, complete with the user form, and say what you are trying to do with the user form (just add new stock items to an existing table, or edit stock items already in the table, or both?).

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Dynamic Userform Design


    Hey guys,


    Ok, so I have been trying to learn all this, but am stuck.


    I have a module which creates the dynamic userform on the fly and builds the appropriate number of boxes. I also have two classes, one that take care of the OnExit from a text box within the frame, the other is to take care of Adding deleting line items. The problem I now have is within the second class...



    QteBuilder is the name of the Sub that builds out the form. What I am trying to achieve is that when a command button named Add is clicked, it should close down "Userform2", delete the form in vba, then run the Macro to add back in "Userform2" again. However, the code crashes even when I have tried changing userform names. Would I be better off deleting all the controls off, then in "QteBuilder" sub checking if userform2 already exists? I have tried adding timers, loops, DoEvents, but nothing seems to change the result.


    The WBRw is the number assigned to the button which also corresponds to the row in my worksheet ie Del65 would correspond to row 65 in my workbench sheet


    Any help appreciated as I'm so close and yet so far!

    Re: Dynamic Userform Design


    Hmm, I'm now in a pickle again. I cant seem to remove the controls from userform2. My idea was to test if Userform2 existed, and if it did remove all the controls on the form, and remove the code, and then replace it with the new values.



    Anybody want to be my best friend and give me a nudge of what to do?! Please!

    Re: Dynamic Userform Design


    Thinking out loud - am I best to userforms.count and then show the last userform to do what I need (ie if I was to add ten items, I would generate 10 new userforms), then write a loop to delete the userforms when finished?


    I really don't understand why I cant just delete and add back in...

    Re: Dynamic Userform Design


    Ok, so after playing around, it seems the userform gets stored in memory even if deleted or renamed.


    So I thought ok fine, lets get the name of the new userform as it is generated. However, going down this avenue gave me another issue with the enter/exit class



    If I change the "Public WithEvents FormCtrl As UserForm2" to "Public WithEvents FormCtrl As UserForm" it wont trigger, and I cant seem to pass the current form object name from the module to the class (ie objForm.name gives me "Userform2", but in the class it doesn't give anything even though ive made it a global variable).


    Please any help?!

    Re: Dynamic Userform Design


    Hmmmmmm, Somewhat behind the 8 ball here, but I have developed a fairly complex userform system that SOUNDS vaguely similar to what you are trying to do. Mine is currently read only (it's for display not editing), but some of the concepts may be transferable.


    I have a userform called ufRecords, which is pretty much a big blank form and some code, and contains a collection of clbentry objects
    I have a classModule clbentry. This class module contains a frame and a "collection" of labels and faux-labels with events.


    The userform has a method to add 1 (or more) "records". It does so by creating a new clbentry object, passing it the position of the LAST clbentry object and the information to go in it. The clbentry object then creates a new frame in the appropriate location, creates a bunch of label objects and puts the new information in them. Critically with objects at the bottom of the "tree" (i.e labels on frames in a clbentry object in the form) experience an event, ALL they do is invoke a method on the parent object and pass it information about the event, and an id, that can be used to identify the object. All events are therefore "dealt" with by the userform. For example - when a label has mouseover, the label registers an event and tells the clbentry object that the object "lHorse", registered a mousemove event, passes it the button,shift,x and y parameters of the event, and the "tag" of the label. clbentry then passes all that information AND a clbentry "tag" to the form. The form then performs the appropriate action (in my case displays text relating to the original label in a giant floating label -like a tool tip)



    Not sure if this makes ANY sense or is likely to be helpful. But *I've* found it to be a helpful way to have a form respond intelligently to events on sub objects created at run time (my form FREQUENTLY has 1000+ clbEntry object, each with ~40 labels, so coding for each of those would be a freaking nightmare).

    Re: Dynamic Userform Design


    Hello,


    Do you have some sample code? The "dynamic" part of the userform is all contained within a frame - its in there that I'm looking to add or delete boxes. Like I say I'm completely stuck on how to update the userform at run time. I only originally designed this to completely delete and rebuild userform as I thought it would be neater, but more than happy to "amend" a userform..


    Many thanks

    Re: Dynamic Userform Design


    Ok,


    I think the issue is with the userform code itself



    I think the "watchevents" is getting stuck in the memory and here where the problem lies. This is why the userform isn't getting deleted



    Here is a mock up - PLEASE NOTE THAT THIS MAY CRASH CURRENTLY! Hopefully gives an idea of what I'm trying to achieve

    Re: Dynamic Userform Design


    I'm doing my own head in now, but yes The problem looks like it is in the userform code. It does delete out, but when stepping through doesn't seem to update until I stop the macro

    Re: Dynamic Userform Design


    Example won't run, missing a stack of sheets and data headers.


    I've looked at the code and I'm struggling to understand. I don't get why you need to "close down "Userform2", delete the form in vba, then run the Macro to add back in "Userform2" again."


    If you instead have a sub that builds a single line, you can call this x time when first run activate the form, and then call it ONE MORE TIME when you need to add.


    If you put each line on it's own frame, you would have the ability to delete a frame, move all "lower" frames up by x pixels to fill the gap, move them down x pixels to create a gap to put a new frame in etc etc etc etc

    Re: Dynamic Userform Design


    Hello Rich,


    My apologies - I had to clear some sensitive data. The attachment above should now work (you need to save it down, and ensure you aren't protected - please mind that this can crash!).


    Ok, so in the example if you click on the home tab, and then new invoice it will ask for customer details (type in Tomy - it actually searches on a list of hundreds and gives back all results. It will then give a pop up with the results - in this example I have only put on one dummy so click the option box. It will then generate lines from the "Workbench sheet" (so normally this would be blank to begin with, but I populated some data to show what I was trying to do).


    Previously when I generated a userform from the vba I was able to simply unload, and then delete out the userform before building the next one. When the userform populates it is set to always give one extra blank line so when the user populates (populating these lines writes them to the workbench tab), they then click the add button so it will close down the userform, read how many rows are populated in workbench tab +1, then regenerate. I thought that would be the easiest way of doingit.


    The form near enough does what I want other than adding or deleting extra "lines" to put more stock in. I have to have this ability as the person using it can "fudge" or override any of the lines. Id rather mot redesign, but im interested to know whats causing the crash

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login