Userform seems to reactivate randomly

[RobVos]

I have a userform that upon activation I resize. Every once in a while it will apparently re-activates and all the controls resize again in the form and become too large to fit on the form. There seems to be no rhyme or reason as to when or why this will happen.

A colleague uses the same workbook. Yesterday he said he had it do this several times. I had it happen to me once time yesterday after not doing it for several days. When it happened to me, I was replying to an email in Outlook. I can never replicate this, even if I do the exact same thing that occurred when it resized.

For now, when it happens, I close the form with the 'X' and reopen it, and all is fine.

What else would cause the userform to reactivate?

The only thing I can think of doing is having a public variable that I set to 1 when the userform first activates and then reset it to zero when the userform is closed normally by the user, then put a statement in the code that if the variable is 1 then exit the activate sub.

Any other ideas, as I am only a casual user of VBA and rely on the help from the program and the internet to figure out things I want to do.

 

[obiron]

how are you 'closing' the userform

The lifecycle of a user form:

Initialise->Activate->Close_Query->Terminate


When you do UserForm.Show the Intialise() event will fire if the form is not already created then the Activate() event

When you do UserForm.Hide this DOES NOT fire the Close_query() or Terminate() events. These are fired when you use the X close button top right. If you want to unload the form manually you have to explicitly call the events from your code.

If the form is initialised but hidden then the Activate() will fire again when the form is re-shown.

Does this help you track down the issues??

 

[RobVos]

When the user closes the form it is via the 'X' in the upper right corner. So it is via the CloseQuery.

The resizing occurs without the form being closed (and it only happens randomly). I never Hide the form. There are times when other forms open and cose, but this form stays open.

Is there something else that would fire the Activate procedure? Maybe something external (like Outlook or Word having something happen in them if they are open on the users computer)?

BTW, the other user is running Windows & and I am running XP, if that is of any consequence.

 

[Norie]

How are you opening the userform?

Is it opened morally?

What code is on it?

How are the other forms opened/closed?

 

[RobVos]

The form is opened when the workbook is opened (once Macros are enabled) by the startup macro. (is this somehow re-firing?)

I use the .Show to open the form. There is also a button on the worksheet that opens the form the same way (this is so the user can close it with the 'x' and reopen it).

Other forms are opened via button on this form to get user input.

There is alot of code associated with the form. The form has several buttons, a listbox and several textboxes that display data from various worksheets. Is there specific code you are interested in seeing (such as the initialize or active code)?

 

[mikerickson]

Do you have any code in the UF that resizes the controls?

 

[RobVos]

Do you have any code in the UF that resizes the controls?

Yes, when the form activates, I resize the form to a little smaller that the size of the application window. I then resize all the controls (this is in the activate procedure).

Apparently this code runs again at times, but I can't figure out why.

 

[mikerickson]

It sounds like the Activate codes does:

1) Look at the window size
2) Make the UF a little bit smaller
3) Make the controls the appropriate size.

Even if it were being fired twice, you shouldn't get the result that some controls are too large for the form.

I suspect that you might be creating some controls or that the Activate code is getting triggered when the ActiveWindow is not what you think it is.

Also, I would want step3) to look only to the size of the UF and not at the Window size to determine the proper size for the controls.

 

[obiron]

as per my post #2,

Activate() will run every time the form is shown.

Initialise() will only run when the form is loaded.

red X unloads the form
any code that .Hide's the form does not unload it natively.

 

[RobVos]

I am not trying to be difficult, and maybe I am not explaining things well enough (I know it is difficult to convey things like this).

The form is only shown via 2 instances:

1) The start-up macro.
2) With the button on the worksheet to open it - that is only available if the user closed the form via the 'x' in the upper right.

Thus, the only time the Activate procedure should run are these times (and this works fine).

Somehow it seems the Activate is running at a time where the form has never been hidden. And I cannot replicate this, it seems to occur randomly. Like I said, I was using it all day yesterday and it only occurred one time.

Here is what was occurring according to my colleague:
One of the buttoms is for adding information into the spreadsheet. It gets a user selected file via

Application.GetOpenFilename( _
fileFilter:="Work Item Name (*.doc*), *.doc*")

I then have code that takes portions of the filename and puts them into a speadsheet.
A small userform is opened with radio button to select a funding code for this new file and closes by Unloading when the user clicks an OK button.. Then another form opens which gets data on an estimate. This form is closed by the user via a button that Unloads the form.

The original form never gets hidden or closed while this occurs, these other forms just open and close over it.

He claims the resize was happening while doing the above, but only every other time or so, not every time.