Userform Knowlegde needed: Doesnt behave the same when closed or when submitted

[clemsoncooz]

I need some help with this from someone with knowledge of userforms.

Please look at the example workbook I have attached and run the macro on sheet 1.

If you work through these (2) scenarios, you will notice that they do not act the same. When I say act, I mean that the userform does not appear to be in the same state at different points - but I would like them to be.

If I run the procedure to its entirety, and then select to run it again, it appears in a different state when compared to NOT running it to entirety and just closing out - THEN trying to re-run it.

Scenario 1:
Run the macro by bringing up the userform.
Select 1 building.
Hit Go.
Enter anything you want for a name (i.e "cvgtbhnjmk"), then enter any number (i.e 50000)
Tab over and hit submit.
Result - the userform enters the data as it should into Sheet1 - then hides iteself.

Scenario 2:
Run the macro by bringing up the userform.
Select 1 building.
Hit Go.
Now CLOSE out of the userform by hitting X in the upper right.
Result - the userform hides itself.

IT IS AT THIS POINT THAT WHEN I HIT THE "bring up userform" FOR A SECOND TIME - that the userform will be in two different states or appear to be different, but would think that they should be the same.

Any knowlegde of why they are different - and how do I solve that problem?

Thanks in advance.

[Leith Ross]

Hello clemsoncooz,

The UserForm hides the controls based upon the number of buildings selected. Once the UserForm is closed, all variables and objects are destroyed. It would help to know want you the UserForm to display after it is first opened.

[clemsoncooz]

Hello Leith Ross and thank you for looking at this.

What I want to be displayed every time the userform opens (regardless if it has run through its entirety before or whether it was closed), I want it to display exactly the same way its displayed the first time its opened.

Meaning - just showing:
Label4 - "How many buildings...."
tbxBuildingcount
SpinButton1
cmdGO

If the user "closes" out with the X, then when they select to bring it back up - it appears the way as needed.
BUT if the user runs through its entirety and then brings it back up, it appears different: meaning Label15 & Label26 is showing. Also tbxBuildingcount has a value which I thought is previously cleared out.

Hello clemsoncooz,

The UserForm hides the controls based upon the number of buildings selected. Once the UserForm is closed, all variables and objects are destroyed. It would help to know want you the UserForm to display after it is first opened.

[clemsoncooz]

I guess I need to add:

Please Login or Register  to view this content.

In Private Sub UserForm_Activate()

[clemsoncooz]

Side Question:
Do you know how to Clear the variables that are not displayed? Meaning if the user spins to "3" and hits Go....then Fills out all the information, but then before hitting submit they decrease the spin from 3 down to 2....how do I clear the variables that are not shwoing?? because if they then hit submit....the variables in Building 3 are still submitted to Sheet1.

[Leith Ross]

Hello Clemsoncooz,

The difference between hiding a UserForm and closing a UserForm is once closed the variable and objects are destroyed. When hidden the variables and objects remain in memory and only the window is removed. You should have a separate routine to initialize the UserForm the way you want. You can then call it whenever you want and not have to rely on a specific event.

[clemsoncooz]

OK - I think I understand that. How do you have a separate routine to initialize it that I am not already doing - isnt this "Private Sub UserForm_Activate()" - just that? Or should it be ""Private Sub UserForm_initialize()." Are those not the same?

Also, did you happen to see my side question on how to clear the variables that are not visible?

[Leith Ross]

Hello Clemsoncooz,

You add create a Sub or Function procedure and add to the top of the UserForm module. Here is an an example.

Please Login or Register  to view this content.

[clemsoncooz]

Thanks Leith Ross! I see what you did there.

On the cmdGO_Click, how do I reset the values equal to zero when X is (tbxBuildingcount +1) through 10?

Otherwise said, all those textboxes & tbxSQFT that are .visible=false ?

[Leith Ross]

Hello Clemsoncooz,

This version of the Reset macro works on my end. Try it out and let me know the results. This goes at the top of the UserForm as in the previous example. Also call this from the UserForm Activate event.

Please Login or Register  to view this content.

[clemsoncooz]

Yes this does work on my end as well, So thank you very much!!

Except for my request for clearing out those variables that are false. So if the user enters in data on say Building 3, but then backs the spin down to 2, it doesnt clear out that variable and still enters it into Sheet.

So I was wondering:
On the cmdGO_Click, how do I reset the values equal to zero when X is (tbxBuildingcount +1) through 10?

Otherwise said, all those textboxes & tbxSQFT that are .visible=false ?

[clemsoncooz]

I think I got it - perhaps you can jsut confirm if correct.

I just place the following code within cmdGO_Click

Please Login or Register  to view this content.

[Leith Ross]

Hello Clemsoncooz,

The idea of hiding the labels and text boxes has had me working on a better. I am putting the building labels and text boxes on to individual Frames. Each Frame is a container object. When the Frame is hidden so are all its controls. This will simplify the code greatly and make it easier to manipulate the UserForm the way you want.