Posts by Cheech22

    I got this figured out. It turns out that I do want to use the default userform instances, as doing so allows me to carry over my variables to the second part of my code event after the first part finishes (the program is iterative, running the same sequence multiple times but with a break in the middle due to me using the worksheet to collect data). When I couldn't "unload" one of my userforms, it was because I hadn't set up the queryclose event correctly, not because I was using the form's default instance. Also, if I wanted to avoid my default instance solution, I'd need to use something like a DoEvents loop, which I definitely do not want to do.



    Variables don't exist after the code is finished.


    It's hard to try to understand what you are doing without seeing the code. Attach an example workbook


    I'm puzzled why you need four UserForms.

    As far as the four-userform construction, each form is dependent on the form that came before it, but since the type of data collected by each form is very different it seemed like creating multiple userforms that send data back to the client code (into classes) was the best way to go. I suppose that I could have just made one userform with four pages, but I feel like the interface would have been clunkier if I had gone that route (also, I didn't know that you could create multi-page forms when I started building this thing.)

    Greetings,


    I have an application that runs off of a series of userforms to collect data. The client code contains a loop construction to get through four userforms before it gets to the end, where the data is transferred to a worksheet. The below code works and is included here mainly to give you an idea of what the macro is doing.


    The above code as been simplified for brevity and it works fine up until this point. What I want to do is have the user enter additional data on the worksheet rather another userform, because the data is constructed such that it's more practical to do it this way, After the user enters their data, they are going to click a button that will trigger another subroutine to save the data in coll1 and coll2.


    The problem is that when the code gets to the worksheet part, the sub is finished running and all the variables and collections and so forth are gone. The funny thing is that this whole macro used to work when I called the forms' default instances, because at least one of them remained in memory while the user did their thing with the worksheet (even though I tried to unload them all). Now that I've rewritten the code to instantiate the forms, everything disappears when the sub gets to the end.


    I'm wondering what the best way is to allow the user to enter their data on the worksheet while the sub is still running. I'm considering two possible solutions: one would be to use the "stop" keyword and have the button on the worksheet resume the code (at which point another sub would be called). The other solution would be to have a modeless userform take the place of the msgbox that gets minimized when the user clicks through it which would (I think) keep the code going. Neither solution seems ideal - I'm fairly new to VBA, so I'm wondering if there's something obvious that I'm not considering.


    Thanks in advance!

    Greetings,


    I have an application that uses a series of userforms. One of my subroutines contains code to unload any forms that are currently loaded so as to reset the project:


    Code
    1. Dim form As UserForm
    2. For Each form In UserForms
    3. Unload form
    4. Next form

    The above code works fine. However, I have another sub that is supposed to unload any loaded userforms except a specific one:


    Code
    1. For Each form In UserForms
    2. If form.Name <> UserForms(LandingPage).Name Then
    3. Unload form
    4. End If
    5. Next form

    With this code I get the ol' runtime error 438 "Object does not support this property or method". I have also tried it this way with the same result:


    Code
    1. For Each form In UserForms
    2. If form.Name <> "LandingPage" Then
    3. Unload form
    4. End If
    5. Next form

    To summarize, I'm trying to unload a series of userforms via a loop, and need to refer to a unique property to avoid unloading a specific userform. It does not appear that I can refer to userform.name in the code (".Name" doesn't even appear as an available property in Intellisense), so I'm wondering how to do this properly.


    Thanks!