Initializing a UserForm

[Don Wells]

Running Excel 2003 SP3

Just now a userform was displayed with unexpected items listed in a list box. It occurs to me that I don't understand what triggers a userform initialize procedure.

Can someone enlighten me?    Please!

TIA

[HansV]

A userform is initialized when it is loaded into memory. You can load it explicitly:

Load UserForm1

and if you show a userform that hasn't been loaded yet, loading it is implicit:

UserForm1.Show

There are two ways you can dismiss a userform: hiding and unloading.

Me.Hide

or

UserForm1.Hide

will make the form invisible while it remains in memory. All controls retain their values. If you next show the userform again:

UserForm1.Show

it will NOT be initialized because it is still loaded in memory. So it'll display the "old" values unless you take care to remove them.

Unload Me

or

Unload UserForm1

will close the form and remove it from memory. So next time you show the form, it will be initialized again.

[Don Wells]

Thank you Hans
    I needed that.    

[Jan Karel Pieterse]

What I usually do is add a public sub called Init to the form, which I can call whenever I see fit. That way I always know when the form will initialize.

When I want to show the form I then do this (air code):

Code: Select all

Dim ufForm as UserForm1
Set ufForm = New UserForm1
With ufForm
.Init
.Show
End With

[Don Wells]

Thank you Jan Karel
    I will keep that in mind.

[Don Wells]

I am about to institute the approach suggested by Jan Karel; primarily as a consequence of an oddity which I have come across. I found that the following code was not initializing my userform.

Code: Select all

Public Sub Activate_FG_Sht()
       
10      If FG_Defn_Sht = "New Item Class Req'd." Then
20        SelTxtCaller = 1
30        frmSelTxt.Show
40      End If
          
End Sub

So I added line 25;

Code: Select all

25        Load frmSelTxt

With line 25, there was no indication that the form was being initialized either when running or when stepping through the code. The next step was to add line 22 to force an initialization at line 25

Code: Select all

22        Unload frmSelTxt

To my amazement line 22 caused the code to branch to frmSelTxt.UserForm_Initialize before getting to the Load command.

Do I have an oddity/corruption in my Excel 2003 installation?
Can other loungers duplicate my observations?

[StuartR]

If the form has been previously loaded, and not unloaded again, then this is the expected behavior.

[HansV]

I too can confirm that this happens, but does it matter?

[Don Wells]

If the form has been previously loaded, and not unloaded again, then this is the expected behavior.

I believe that it does matter. If I had understood this earlier, several hours would have been spent more usefully.

[Don Wells]

The following code was provided by Jan Karel earlier in this thread.

Code: Select all

    Dim ufForm as UserForm1
    Set ufForm = New UserForm1
    With ufForm
    .Init
    .Show
    End With

Can someone advise me on the benefit of the term 'New' in the 2nd line?

T.I.A.

[HansV]

The line

Code: Select all

    Set ufForm = New UserForm1

creates a new instance of UserForm1. So regardless of whether an instance of UserForm1 is still in memory (because it has only been hidden, not unloaded) or not, the UserForm_Initialize code will run for ufForm.

[Don Wells]

Thank you Hans.