UserForm_Initialize
 

Have you considered "UserForm_Activate" instead? That could be much easier to
control.

Normally, before showing a form, if you want to ensure the
UserForm_Initialize is fired, there are two main things you can choose to do:
1) before showing the form, you unload it. This ensures the form is
unloaded. For example:

On error resume next
unload userform1
on error goto 0
load userform1
userform1.show

2) At the end of your macro, unload all forms. For example:

On error resume next
unload userform1
on error goto 0


"UserForm_Initialize" does have its importance. It is normally used for
preparing your userform for being used. These may include:
1) adding items to listboxes and comboboxes,
2) setting the visibility of controls, etc.

Such tasks could be less efficient to be put into "UserForm_Activate".
Imagine, if you got a form which will be show/hide/show several times during
operaion (e.g. a wizard), and you got a dropdown list of 12 months (Jan, Feb
.... Dec) and then a dropdown list containing years from 1980 to 2050. If you
put the additem statements into "UserForm_Activate", the dropdown lists will
be cleared and population every time the form is shown. But this is in fact
unnecessary. Why doing it many times? Why not just once?

In the example, if you put the additem code into "UserForm_Initialize", they
will be executed only once. Then you can use "UserForm_Activate" to set the
listindex of those dropdown lists before the form is actually displayed.

Regards,
Edwin Tam

http://www.vonixx.com




"RB Smissaert" wrote:

No particular question really, but just an observation and maybe somebody
has some opinion about this.
When you have a complex VBA project with userforms it can be difficult to
control when the UserForm_Initialize
event will be triggered. I have found now that it is just much easier not to
use this at all and make a procedure
that runs all the code that normally would go into UserForm_Initialize.
Then you can run this procedure whenever you want, for example after Load
UserForm.
Doing this gives you me more control and avoids problems.

RBS

UserForm_Initialize
 

Yes, I considered that, but this form doesn't normally get closed in that
particular
Excel session, but instead gets minimized via the normal Windows minimize
controls added via the API. So, I don't hide the form, but either it gets
minimized
by the user or gets unloaded fully.
Doing Load UserForm doesn't take long, so it would be no problem to do this
again, rather than UserForm.Show of a hidden form.
The only thing I do in the activate event is set the position of the form on
the screen.

RBS



"Edwin Tam" wrote in message
...
Have you considered "UserForm_Activate" instead? That could be much easier
to
control.

Normally, before showing a form, if you want to ensure the
UserForm_Initialize is fired, there are two main things you can choose to
do:
1) before showing the form, you unload it. This ensures the form is
unloaded. For example:

On error resume next
unload userform1
on error goto 0
load userform1
userform1.show

2) At the end of your macro, unload all forms. For example:

On error resume next
unload userform1
on error goto 0


"UserForm_Initialize" does have its importance. It is normally used for
preparing your userform for being used. These may include:
1) adding items to listboxes and comboboxes,
2) setting the visibility of controls, etc.

Such tasks could be less efficient to be put into "UserForm_Activate".
Imagine, if you got a form which will be show/hide/show several times
during
operaion (e.g. a wizard), and you got a dropdown list of 12 months (Jan,
Feb
... Dec) and then a dropdown list containing years from 1980 to 2050. If
you
put the additem statements into "UserForm_Activate", the dropdown lists
will
be cleared and population every time the form is shown. But this is in
fact
unnecessary. Why doing it many times? Why not just once?

In the example, if you put the additem code into "UserForm_Initialize",
they
will be executed only once. Then you can use "UserForm_Activate" to set
the
listindex of those dropdown lists before the form is actually displayed.

Regards,
Edwin Tam

http://www.vonixx.com




"RB Smissaert" wrote:

No particular question really, but just an observation and maybe somebody
has some opinion about this.
When you have a complex VBA project with userforms it can be difficult to
control when the UserForm_Initialize
event will be triggered. I have found now that it is just much easier not
to
use this at all and make a procedure
that runs all the code that normally would go into UserForm_Initialize.
Then you can run this procedure whenever you want, for example after Load
UserForm.
Doing this gives you me more control and avoids problems.

RBS