UserForm Initialize event doesn't fire reliably

[RB Smissaert]

Finally discovered that the Initialize event of VBA userforms doesn't always
fire at the time when you expect it to fire.
I had some strange bugs that I couldn't figure out as I just presumed that
if you did Load Userform1 the initialize event
would fire and also that it would fire at no other occasion.
I found that for example it could fire when you do:

If Userform1.Visible = True Then

I have no completely bypassed the Initialize event and just made a normal
Sub that does the same and run this
just before doing Load Userform1.
Seems that this is much better.
I am dealing with a large complicated .xla add-in here and maybe the above
doesn't apply to simpler projects.


RBS

[Tom Ogilvy]

If you refer to userform1 and it is not loaded, that reference loads it and
fires the initialize event. So I would expect it works exactly as
advertised, but we, the user, are sometimes caught unawares.

> If Userform1.Visible = True Then

if userform1 was not currently loaded during execution of this piece of
code, it would be loaded at that time.


--
Regards,
Tom Ogilvy



"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> Finally discovered that the Initialize event of VBA userforms doesn't
always
> fire at the time when you expect it to fire.
> I had some strange bugs that I couldn't figure out as I just presumed that
> if you did Load Userform1 the initialize event
> would fire and also that it would fire at no other occasion.
> I found that for example it could fire when you do:
>
> If Userform1.Visible = True Then
>
> I have no completely bypassed the Initialize event and just made a normal
> Sub that does the same and run this
> just before doing Load Userform1.
> Seems that this is much better.
> I am dealing with a large complicated .xla add-in here and maybe the above
> doesn't apply to simpler projects.
>
>
> RBS
>

[sebastienm]

Hi,
Usually when you have a class say Class1, the Initialize event is fired when
an object of that calss is created (ie when some space is allocated in
memory). Eg:
Dim a as Class1 '<--- declaration, object not created yet
Set a = new Class1 '<--- object created, Initialize fires
With userforms it is similar, except one thing which can be a bit confusing.
Think about the form you have designed as a class (and it IS one), say
UserForm1. When you use the word Userform1 in a sub, vba automatically
creates a variable Userform1, ie a variable that has the same name as its
class. This is a default behavior. That is, vba creates the object the first
time you use it in your code and this is when the Initialize fires. It can be
because of a Show, a Visible, or a Caption request.
If you prefer to manage the behavior by yourself, consider using:
Dim frm as Userform1
Set frm = New Userform1 '<-- Initialize fires
Also, to show that USerForm1 is a class like any other one, you can create
several objects of that class at the same time (put a msgbox in the
Initialize event of the userform):
Dim frm1 as Userform1, frm2 as Userform1
Set frm1=new userform1 '<-- initialize fires for frm1
frm1.Show vbModeless '<-- show frm1
Set frm2 = new userform1 '<-- initialize fires for frm2
frm2.show vbmodeless '<-- show frm2 while frm1 is still showed

Hopefully, i was clear enough,
Sebastien

"RB Smissaert" wrote:

> Finally discovered that the Initialize event of VBA userforms doesn't always
> fire at the time when you expect it to fire.
> I had some strange bugs that I couldn't figure out as I just presumed that
> if you did Load Userform1 the initialize event
> would fire and also that it would fire at no other occasion.
> I found that for example it could fire when you do:
>
> If Userform1.Visible = True Then
>
> I have no completely bypassed the Initialize event and just made a normal
> Sub that does the same and run this
> just before doing Load Userform1.
> Seems that this is much better.
> I am dealing with a large complicated .xla add-in here and maybe the above
> doesn't apply to simpler projects.
>
>
> RBS
>
>

[Tushar Mehta]

Ummm...I suspect the event works as intended. When you test
userform1.visible, if the userform is not loaded, it must be loaded.
That triggers the Initialize event. Here's a simple test.

Create a userform with two buttons. Add the following code to the
userform module:

Option Explicit

Private Sub CommandButton1_Click()
Me.Hide
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
MsgBox "UserForm_Initialize"
End Sub

In a standard module, add the following:
Option Explicit

Sub testIt()
UserForm1.Show
End Sub
Sub testVisible()
MsgBox UserForm1.Visible
End Sub

Test 1: Run testIt. You will get the Userform_Initialize message. Use
Commandbutton1 to just hide the userform. Run testVisible. You will
only get the False message from the .Visible test.

Test 2: Run testIt. This time use Commandbutton2 to unload the
userform. Now, when you run testVisible you will get both the
_Initialize message and the False message.

Basically, the Initialize event procedure is run each time the userform
is initialized (i.e., loaded) -- and only when it is initialized.
Finally, keep in mind that a loaded userform may or may not be visible.
And, you cannot test any property of a userform unless it is loaded.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Finally discovered that the Initialize event of VBA userforms doesn't always
> fire at the time when you expect it to fire.
> I had some strange bugs that I couldn't figure out as I just presumed that
> if you did Load Userform1 the initialize event
> would fire and also that it would fire at no other occasion.
> I found that for example it could fire when you do:
>
> If Userform1.Visible = True Then
>
> I have no completely bypassed the Initialize event and just made a normal
> Sub that does the same and run this
> just before doing Load Userform1.
> Seems that this is much better.
> I am dealing with a large complicated .xla add-in here and maybe the above
> doesn't apply to simpler projects.
>
>
> RBS
>
>

[Tushar Mehta]

In article <[email protected]>,
[email protected] says...
{snip}
> Also, to show that USerForm1 is a class like any other one, you can create
> several objects of that class at the same time (put a msgbox in the
> Initialize event of the userform):
> Dim frm1 as Userform1, frm2 as Userform1
> Set frm1=new userform1 '<-- initialize fires for frm1
> frm1.Show vbModeless '<-- show frm1
> Set frm2 = new userform1 '<-- initialize fires for frm2
> frm2.show vbmodeless '<-- show frm2 while frm1 is still showed
>
Nice example. Though, while a UF is a class it is a special class. I
remember running into some differences between a UF and a normal class
some time ago though I cannot recall what.

One that comes to mind and I just tested is that one can 'Load UF1' but
not 'Load Class1'.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Hi,
> Usually when you have a class say Class1, the Initialize event is fired when
> an object of that calss is created (ie when some space is allocated in
> memory). Eg:
> Dim a as Class1 '<--- declaration, object not created yet
> Set a = new Class1 '<--- object created, Initialize fires
> With userforms it is similar, except one thing which can be a bit confusing.
> Think about the form you have designed as a class (and it IS one), say
> UserForm1. When you use the word Userform1 in a sub, vba automatically
> creates a variable Userform1, ie a variable that has the same name as its
> class. This is a default behavior. That is, vba creates the object the first
> time you use it in your code and this is when the Initialize fires. It can be
> because of a Show, a Visible, or a Caption request.
> If you prefer to manage the behavior by yourself, consider using:
> Dim frm as Userform1
> Set frm = New Userform1 '<-- Initialize fires
> Also, to show that USerForm1 is a class like any other one, you can create
> several objects of that class at the same time (put a msgbox in the
> Initialize event of the userform):
> Dim frm1 as Userform1, frm2 as Userform1
> Set frm1=new userform1 '<-- initialize fires for frm1
> frm1.Show vbModeless '<-- show frm1
> Set frm2 = new userform1 '<-- initialize fires for frm2
> frm2.show vbmodeless '<-- show frm2 while frm1 is still showed
>
> Hopefully, i was clear enough,
> Sebastien
>
> "RB Smissaert" wrote:
>
> > Finally discovered that the Initialize event of VBA userforms doesn't always
> > fire at the time when you expect it to fire.
> > I had some strange bugs that I couldn't figure out as I just presumed that
> > if you did Load Userform1 the initialize event
> > would fire and also that it would fire at no other occasion.
> > I found that for example it could fire when you do:
> >
> > If Userform1.Visible = True Then
> >
> > I have no completely bypassed the Initialize event and just made a normal
> > Sub that does the same and run this
> > just before doing Load Userform1.
> > Seems that this is much better.
> > I am dealing with a large complicated .xla add-in here and maybe the above
> > doesn't apply to simpler projects.
> >
> >
> > RBS
> >
> >
>

[RB Smissaert]

Thanks for all the replies.
I learned something very fundamental there and will now have a good look
at my code to see if something needs altering. I think many VBA programmers
may have learned from this.

RBS


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> Finally discovered that the Initialize event of VBA userforms doesn't
> always fire at the time when you expect it to fire.
> I had some strange bugs that I couldn't figure out as I just presumed that
> if you did Load Userform1 the initialize event
> would fire and also that it would fire at no other occasion.
> I found that for example it could fire when you do:
>
> If Userform1.Visible = True Then
>
> I have no completely bypassed the Initialize event and just made a normal
> Sub that does the same and run this
> just before doing Load Userform1.
> Seems that this is much better.
> I am dealing with a large complicated .xla add-in here and maybe the above
> doesn't apply to simpler projects.
>
>
> RBS

[RB Smissaert]

Thanks, learned something very important there?
How would I check for a userform being visible or loaded without the check
causing the form to load?

RBS


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> If you refer to userform1 and it is not loaded, that reference loads it
> and
> fires the initialize event. So I would expect it works exactly as
> advertised, but we, the user, are sometimes caught unawares.
>
>> If Userform1.Visible = True Then
>
> if userform1 was not currently loaded during execution of this piece of
> code, it would be loaded at that time.
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
>> Finally discovered that the Initialize event of VBA userforms doesn't
> always
>> fire at the time when you expect it to fire.
>> I had some strange bugs that I couldn't figure out as I just presumed
>> that
>> if you did Load Userform1 the initialize event
>> would fire and also that it would fire at no other occasion.
>> I found that for example it could fire when you do:
>>
>> If Userform1.Visible = True Then
>>
>> I have no completely bypassed the Initialize event and just made a normal
>> Sub that does the same and run this
>> just before doing Load Userform1.
>> Seems that this is much better.
>> I am dealing with a large complicated .xla add-in here and maybe the
>> above
>> doesn't apply to simpler projects.
>>
>>
>> RBS
>>
>
>

[RB Smissaert]

OK, found a function from Jake Marx that will show this:

Function bIsFrmLoaded(rsFrmName As String) As Boolean

Dim nIdx As Integer

For nIdx = 0 To UserForms.Count - 1
If StrComp(UserForms(nIdx).Name, rsFrmName, _
vbTextCompare) = 0 Then
bIsFrmLoaded = True
Exit For
End If
Next nIdx

End Function

But I suppose it is best just to keep track of this with a public variable.

RBS


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> If you refer to userform1 and it is not loaded, that reference loads it
> and
> fires the initialize event. So I would expect it works exactly as
> advertised, but we, the user, are sometimes caught unawares.
>
>> If Userform1.Visible = True Then
>
> if userform1 was not currently loaded during execution of this piece of
> code, it would be loaded at that time.
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
>> Finally discovered that the Initialize event of VBA userforms doesn't
> always
>> fire at the time when you expect it to fire.
>> I had some strange bugs that I couldn't figure out as I just presumed
>> that
>> if you did Load Userform1 the initialize event
>> would fire and also that it would fire at no other occasion.
>> I found that for example it could fire when you do:
>>
>> If Userform1.Visible = True Then
>>
>> I have no completely bypassed the Initialize event and just made a normal
>> Sub that does the same and run this
>> just before doing Load Userform1.
>> Seems that this is much better.
>> I am dealing with a large complicated .xla add-in here and maybe the
>> above
>> doesn't apply to simpler projects.
>>
>>
>> RBS
>>
>
>

[Tom Ogilvy]

possibly check the userforms collection - if it isn't a member, then it
isn't loaded and isn't visible. If it is a member, then it is already
loaded and you can check the visibility.

--
Regards,
Tom Ogilvy


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> Thanks, learned something very important there?
> How would I check for a userform being visible or loaded without the check
> causing the form to load?
>
> RBS
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > If you refer to userform1 and it is not loaded, that reference loads it
> > and
> > fires the initialize event. So I would expect it works exactly as
> > advertised, but we, the user, are sometimes caught unawares.
> >
> >> If Userform1.Visible = True Then
> >
> > if userform1 was not currently loaded during execution of this piece of
> > code, it would be loaded at that time.
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "RB Smissaert" <[email protected]> wrote in message
> > news:[email protected]...
> >> Finally discovered that the Initialize event of VBA userforms doesn't
> > always
> >> fire at the time when you expect it to fire.
> >> I had some strange bugs that I couldn't figure out as I just presumed
> >> that
> >> if you did Load Userform1 the initialize event
> >> would fire and also that it would fire at no other occasion.
> >> I found that for example it could fire when you do:
> >>
> >> If Userform1.Visible = True Then
> >>
> >> I have no completely bypassed the Initialize event and just made a
normal
> >> Sub that does the same and run this
> >> just before doing Load Userform1.
> >> Seems that this is much better.
> >> I am dealing with a large complicated .xla add-in here and maybe the
> >> above
> >> doesn't apply to simpler projects.
> >>
> >>
> >> RBS
> >>
> >
> >
>

[RB Smissaert]

OK, checking for the loaded status by looping through the userform
collection is a bit slow.
So perhaps it is best to keep track of the loaded status with a public
boolean variable set by
the Initialize and Terminate events and then check for Visible if the loaded
status is True.

RBS


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> possibly check the userforms collection - if it isn't a member, then it
> isn't loaded and isn't visible. If it is a member, then it is already
> loaded and you can check the visibility.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks, learned something very important there?
>> How would I check for a userform being visible or loaded without the
>> check
>> causing the form to load?
>>
>> RBS
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > If you refer to userform1 and it is not loaded, that reference loads it
>> > and
>> > fires the initialize event. So I would expect it works exactly as
>> > advertised, but we, the user, are sometimes caught unawares.
>> >
>> >> If Userform1.Visible = True Then
>> >
>> > if userform1 was not currently loaded during execution of this piece
>> > of
>> > code, it would be loaded at that time.
>> >
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "RB Smissaert" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Finally discovered that the Initialize event of VBA userforms doesn't
>> > always
>> >> fire at the time when you expect it to fire.
>> >> I had some strange bugs that I couldn't figure out as I just presumed
>> >> that
>> >> if you did Load Userform1 the initialize event
>> >> would fire and also that it would fire at no other occasion.
>> >> I found that for example it could fire when you do:
>> >>
>> >> If Userform1.Visible = True Then
>> >>
>> >> I have no completely bypassed the Initialize event and just made a
> normal
>> >> Sub that does the same and run this
>> >> just before doing Load Userform1.
>> >> Seems that this is much better.
>> >> I am dealing with a large complicated .xla add-in here and maybe the
>> >> above
>> >> doesn't apply to simpler projects.
>> >>
>> >>
>> >> RBS
>> >>
>> >
>> >
>>
>
>

[Peter T]

> OK, checking for the loaded status by looping through the userform
> collection is a bit slow.

Is it? - must have a lot of forms!

> So perhaps it is best to keep track of the loaded status with a public
> boolean variable set by
> the Initialize and Terminate events and then check for Visible if the
> loaded status is True.

FWIW, not directly related to your requirement of checking a particular
form, but I have found that checking the Userforms collection to be slightly
more reliable than checking a public variable that "should" have been reset
when the form was unloaded. Purpose - to know if my app is running.

Not sure about later versions but certainly in XL97, maybe also in XL2k, the
situation can arise when user's un-error handled UDF causes Form code to
simply stop, and all normal module public variables remain as were (can
occur even with calc-manual in rare scenarios).

A simplified version of the code you posted -

If Userforms.Count Then ' app is running

Regards,
Peter T

[RB Smissaert]

> If Userforms.Count Then ' app is running

Sorry, didn't get that one.

RBS

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
>> OK, checking for the loaded status by looping through the userform
>> collection is a bit slow.
>
> Is it? - must have a lot of forms!
>
>> So perhaps it is best to keep track of the loaded status with a public
>> boolean variable set by
>> the Initialize and Terminate events and then check for Visible if the
>> loaded status is True.
>
> FWIW, not directly related to your requirement of checking a particular
> form, but I have found that checking the Userforms collection to be
> slightly
> more reliable than checking a public variable that "should" have been
> reset
> when the form was unloaded. Purpose - to know if my app is running.
>
> Not sure about later versions but certainly in XL97, maybe also in XL2k,
> the
> situation can arise when user's un-error handled UDF causes Form code to
> simply stop, and all normal module public variables remain as were (can
> occur even with calc-manual in rare scenarios).
>
> A simplified version of the code you posted -
>
> If Userforms.Count Then ' app is running
>
> Regards,
> Peter T
>
>

[Peter T]

> > If Userforms.Count Then ' app is running
>
> Sorry, didn't get that one.

Yes, I wasn't very clear! I didn't mean to suggest this single line was a
substitute for the full code.

Rather, for an app that whilst running will always have at least one
Userform loaded, if Userforms.Count is zero confirms that the app is not
running, otherwise it is.

As I said it was a FWIW, not directly related to your needs but a usage of
querying the collection for a particular purpose.

Regards,
Peter T

[Peter T]

> > If Userforms.Count Then ' app is running
>
> Sorry, didn't get that one.

Yes, I wasn't very clear! I didn't mean to suggest this single line was a
substitute for the full code.

Rather, for an app that whilst running will always have at least one
Userform loaded, if Userforms.Count is zero confirms that the app is not
running, otherwise it is.

As I said it was a FWIW, not directly related to your needs but a usage of
querying the collection for a particular purpose.

Regards,
Peter T