UserForm_Initialize

[RB Smissaert]

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

 

[Bob Phillips]

I agree Bart.

I tend to have a procedure outside of the userform that loads the form and
does all the initialise biz, then shows it. I find it more manageable.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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
(e-mail address removed)
http://www.vonixx.com

 

[RB Smissaert]

Hi Bob,

Thanks for confirming.
In fact I still had one thing in UserForm_Initialize.
Simplified:

Private WithEvents xlApp As Excel.Application

Private Sub UserForm_Initialize()

Set xlApp = Excel.Application

End Sub

But I have now taken this out as well:

Private lMainFormHwnd As Long
Private WithEvents xlApp As Excel.Application

Public Property Let propFormHwnd(lHwnd As Long)

lMainFormHwnd = lHwnd

Set xlApp = Excel.Application

End Property

Public Property Get propFormHwnd() As Long
propFormHwnd = lMainFormHwnd
End Property

Where the property propFormHwnd gets set in the procedure that replaces
UserForm_Initialize.
I needed the hwnd of this form in any case so I used this the Property Let
procedure.

RBS

 

[RB Smissaert]

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