Dynamic Userform Design

    Re: Dynamic Userform Design


    hmmmmmm, I REALLY don't like some of what you're doing, and it's hard to tell if thats just "not the way I'd do it" or actually wrong.


    Part of your problem could be the unload userform2 command in the cmbClass module. I THINK, the instance of the is object is part of the userform2 object, so when you unload userform2 you are attempting to unload something that is currently executing.


    When I try it, if I move the msgbox unloading to ABOVE the unload command, I SEE the msg before excel dies, I don't see the message after. So the unload is dying, and I SUSPECT it's sying because you are loading something running.

    Re: Dynamic Userform Design


    Praise indeed! I know its a little out of the box, but i thought the concept at least was good in this scenario (and yes i live in a world where you dont need to over ride as thats where problems turn up).


    I have an idea. Like you i think the issue is the class module as somehow something is getting locked and then its trying to open a userform within a userform. Maybe what i need to do is for the buttons to declare a variable (like add button declares this as true) then when the userform unloads from the quotebuilder sub, to then try an if statement whether to redo or stop.


    Let me play!

    Re: Dynamic Userform Design


    Another day, and still no fix.


    Class Module


    WatchEventsCls


    userform Code


    An object somewhere isn't getting cancelled as the form keeps running, and I cant find where.

    Re: Dynamic Userform Design


    It doesnt seem to matter, i tried in the class, on the userform and in the qtebuilder module. It works if i dont use the watchevents code on the userform but as soon as i do, it seems something isnt getting cancelled even though ive set the objects to nothing. But i need the watchevents code as that is what triggers off updates for the text boxes, and that is what my orginal query had an issue with (the fact that if i write code for each text box change, the userform module could grow too big hence the use of class).


    im sure its a simple issue somewhere

    Re: Dynamic Userform Design


    I must admit I'm still unclear on a couple of things.


    First, why exactly can't you use a listbox? It would simplify your life greatly and still display every item as it's entered.
    Second, why are you adding and removing VBComponents? It's much easier to just have a form in the workbook and add or remove controls dynamically as required.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Dynamic Userform Design


    Hello Rory,


    Though i know my way round sheets, ive barely used forms, and never a listbox, so its more down to lack of experience on this. With regards to removing components, it was just to keep the size of workbook down and make one when needed. The person using it doesnt like spreadsheets (barely can use a computer) so a form was thought of. They also want to see the invoice in its entirety before it gets generated, hence why i need to add boxes for more lines of stock. An invoice could have anything from 1 to 120 "lines" of stock, so given that each line has 12 text boxes, than could mean 12 to nearly 1500 boxes, and if i added in code automatically for each box, theres a danger the userform code got too big. I thought the userform itself was quite funky so appreciate this is a little left field.


    Now im just wanting to know why excel is crashing as i thought the logic, though maybe complicated would work. On the example, it crashes and ive now spent days trying to figure out why. If we forget the closing and reopening, it looks like the qtebuilder sub doesnt fire all the way through anyway, and that only happens when i add in the watch events code on the userform (the qte builder sub adds in userform code automatically).


    i know you may think im mad to do it this way, but can anyone humour a mad man all the same?!

    Re: Dynamic Userform Design


    Okay, a couple of thoughts - It's been a while since I've done the theory, so bear with me (and mistakes are possible). You possibly already know some or all of this


    The userform2 and CtlExitCls are both "classes". When they are "instantiated" (usually by set x = new userform2 type command), an object is created from that class. That object has it's own code, methods events etc. When you create a userform2 OBJECT, this also creates ctlExitCls objects BELONGING to that object. If/when the form is unloaded, the object is destroyed, as all all objects belonging to it, and all code, methods, etc of those objects. no code on the userform object (or any subobjects) will run after the form is unloaded. unloading must be the VERY last thing you do in code relating to the object in any way. I don't believe (based on the examples I've seen), that this is what you are doing. I think you have code that belongs to a subobject of the form that unloads the form mid execution, thus destroying itself while it still has lines to execute. This can cause anything from vba exceptions to excel crashing.

    Re: Dynamic Userform Design


    Hi Rich,


    Thanks for sticking with me!


    Ok, I thought that's what the CleanUp code was doing, within the QuteBuilder Sub


    Code
    With objForm.CodeModule
       Line = .CountOfLines
        .InsertLines Line + 1, "Private Sub CleanUp()"
        .InsertLines Line + 2, "bFormUnloaded = True"
        .InsertLines Line + 3, "RaiseEvent OnExit(oPrevActiveCtl, bCancel)"
        .InsertLines Line + 4, "Set oXitClass = Nothing"
        .InsertLines Line + 5, "Set oCol = Nothing"
        .InsertLines Line + 6, "Set oPrevActiveCtl = Nothing"
        .InsertLines Line + 7, "End Sub"
End With


    So on terminating the userform, this code is meant to set all the objects to nothing before then unloading the form (Is that right?) However, if you play Sub "QteBuilder"it will display the user form BUT it wont return back to the sub even if you terminate the userform or assign a unload me to a command button (if you play the sub and close the userform by clicking the x in top right, you will see the vba still shows running so you have to click stop in vba). I have even tried fudging by loading the userform rather than the sub, but it still isn't playing ball (ive tried redoing this dozens of time but I'm bit out of my depth as to what is wrong so I'm hoping someone smarter than me can help!)


    If I can find what or why this is a problem, the rest of the logic should then work which means I can then simply call "Qtebuilder" again to build out the form (as it will read the rows in Workbench tab, and then build out the text boxes plus one blank "line" for the next row of data to be populated). That's my theory anyway...!

    Re: Dynamic Userform Design


    Inserting VBA code on the fly is........... complex and RARELY a good idea. I've done it, usually with fairly complex addins and/or dictator applications, but.... its messy.
    1) It's not debuggable. like at all.
    2) It's REALLY difficult to work out what your code is doing when. Aborting the vba at different points I've ended up with 6 userform objects in vba, and no way of telling what code is still running.
    3) Can you explain why your above code snippet needs to be added to the objform codemodule at run time rather than design time?

    Re: Dynamic Userform Design


    Well the class module was referring to userform 2, so if i needed to add a line of stock, it would add a userform and the class module wouldnt then work. I thought by deleting the form essentially a new form would always be userform2 so the class module would then work...


    does that make sense?

    Re: Dynamic Userform Design


    Quote from M40wen;785245

    Though i know my way round sheets, ive barely used forms


    And you thought you'd start out with this?? ;)


    Quote

    With regards to removing components, it was just to keep the size of workbook down


    Having a small blank userform is really not going to have much impact on size and would make your life much much easier


    Quote

    They also want to see the invoice in its entirety before it gets generated, hence why i need to add boxes for more lines of stock. An invoice could have anything from 1 to 120 "lines" of stock, so given that each line has 12 text boxes, than could mean 12 to nearly 1500 boxes, and if i added in code automatically for each box, theres a danger the userform code got too big.


    All the more reason to use a listbox to hold all the 'rows' and just 10 textboxes for editing the current selection.


    Quote

    Now im just wanting to know why excel is crashing as i thought the logic, though maybe complicated would work. On the example, it crashes and ive now spent days trying to figure out why.


    Trying to change the design of a project while code is running in the same project is, IMO, a disaster waiting to happen. Especially if you are removing components that are explicitly referred to by other components. Even if you intend to persist with your thousands of textboxes approach, I would suggest that you have the userform already present in the project with any code it requires. The run-time controls can be handled with a class or two as you know, so the actual code you need to add to the form should be pretty small.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Dynamic Userform Design


    Well I now have solved the crashing problem (I changed the watchevent to trigger on activate rather than layout and it seems to solve it), but the userform text boxes doesn't update correctly when running. I seem to be swapping one problem for another with this!

Participate now!

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

Register Yourself Login